Содержание
SQL007. Отбор множества записей по ключам из другой таблицы (обход конструкции IN)
Описание
Если требуется отобрать записи из таблицы, одно из полей которых FIELD1 должно принадлежать заданному множеству значений, то лучше не использовать конструкцию IN в условии WHERE, т.к. парсер запросов внутри себя превращает ее в конструкцию OR(FIELD1 = <…>)OR(FIELD1 = <…>)OR(FIELD1 = <…>)……….OR(FIELD1 = <…>).
Гораздо эффективней по скорости выборки данных способ предложенный Ded-ом:
- Список значений упаковывается в строковую константу с разделителем: как правило, мало-используемым символом, например: ~ (тильда).
- В условии WHERE ищется вхождение значения поля FIELD1 текущей записи в список значений.
Например:
SELECT T.FIEDL1, T.FIEDL2, ...., T.FIEDLN FROM MY_TABLE T WHERE ('~1~22~4~555~6~7~8~99~0~54~3324~' CONTAINING '~' || T.FIELD1 || '~' )
На небольшом наборе отбираемого множества значений или статически заданном множестве прирост в скорости не так ощутим, как например в тех случаях, когда отбираемый набор формируется при помощи оператора SELECT, например как WHERE (T.FIELD1 IN (SELECT T2.ID FROM MY_TABLE2 T2)). Если же требуется отобрать значения из древовидного справочника MY_TABLE2 учитывая вложенность записей, то скорость выполнения оператора SELECT возрастает в разы !
Например: пусть существуют в базе данных группирующая «древовидная» таблица MY_GROUP_TABLE и таблица с записями MY_TABLE.
CREATE TABLE MY_GROUP_TABLE ( ID INTEGER NOT NULL PRIMARY KEY ,NAME VARCHAR(50) ,PARENT_ID INTEGER ); CREATE TABLE MY_TABLE ( ID INTEGER NOT NULL ,NAME VARCHAR(50) ,GROUP_ID INTEGER NOT NULL FOREIGN KEY REFERENCES MY_GROUP_TABLE(ID) );
Тогда, чтобы отобрать записи из таблицы MY_TABLE начиная от заданного узла GROUP_ID и учитывая все вложенные узлы из таблицы MY_GROUP_TABLE
CREATE OR ALTER PROCEDURE MY_PROC( Q_GROUP_ID INTEGER )RETURNS( ID INTEGER ,NAME VARCHAR(50) ,GROUP_ID INTEGER )AS DECLARE VARIABLE IDS BLOB SUBTYPE TEXT; /* BLOB потому, что фиг его знает, сколько записей отберется */ BEGIN -- 1. Формируем список значений для отбора, учитывая вложенность WITH RECURSIVE TREE_STMT AS ( SELECT T1.ID, T1.PARENT_ID FROM MY_GROUP_TABLE T1 WHERE (T1.ID = :Q_GROUP_ID) UNION ALL SELECT T2.ID, T2.PARENT_ID FROM MY_GROUP_TABLE T2, TREE_STMT T3 WHERE (T2.PARENT_ID = T3.ID) ) SELECT '~' || LIST(REF.ID, '~') || '~' FROM TREE_STMT REF INTO :IDS; -- 2. Отбираем значения из таблицы MY_TABLE FOR SELECT TB.ID, TB.NAME, TB.GROUP_ID FROM MY_TABLE TB WHERE (:IDS CONTAINING '~'||TB.GROUP_ID||'~' ) INTO :ID, :NAME, :GROUP_ID DO SUSPEND; END
См. также
Источник
Конференция разработчиков Firebird, автор: Ded
http://ibase.ru/ - другое применение указанного способа
Обсуждение
Прочитал статью и с уверенностью, что запрос select sum(total) from any_table ic where datefield > '1.9.7' and ic.unqnumber in (select o.unqnumber from table1 o where code < 90
будет выполняться медленнее, чем
select sum(total) from any_table where datefield > '1.9.7' and (select trim(in_sttmt) from build_in_statement('select unqnumber from table1 where code < 90')) containing '~'||unqnumber||'~';
Результаты оказались наоборот. Во втором случае запрос выполняется за 23 секунды, а в первом - 7. Версия firebird 2.0.
build_in_statement - процедурка, которая принимает строку - select и формирует строку вида '~val1~val2~…'. Вот она: begin
in_sttmt = ''; FOR execute statement :select_statement INTO :val do in_sttmt = in_sttmt || '~' ||val; in_sttmt = in_sttmt || '~'; SUSPEND;end
Под выборку попадают около 70000 записей из нескольких миллионов. Из таблицы table1 выбирается 55 значений.
Прочитал статью и с уверенностью, что запрос select sum(total) from any_table ic where datefield > '1.9.7' and ic.unqnumber in (select o.unqnumber from table1 o where code < 90
будет выполняться медленнее, чем
select sum(total) from any_table where datefield > '1.9.7' and (select trim(in_sttmt) from build_in_statement('select unqnumber from table1 where code < 90')) containing '~'||unqnumber||'~';
Результаты оказались наоборот. Во втором случае запрос выполняется за 23 секунды, а в первом - 7. Версия firebird 2.0.
build_in_statement - процедурка, которая принимает строку - select и формирует строку вида '~val1~val2~…'. Вот она: begin
in_sttmt = ''; FOR execute statement :select_statement INTO :val do in_sttmt = in_sttmt || '~' ||val; in_sttmt = in_sttmt || '~'; SUSPEND;end
Под выборку попадают около 70000 записей из нескольких миллионов. Из таблицы table1 выбирается 55 значений.
1) Строку значений '~1~2~3~4~5~6~7~' нужно приготовить заранее: либо передать запросу строковой константой, либо приготовить как в примере с PSQL-блоком. Я не знаю всех тонкостей оптимизатора 2.0, но возможно у тебя суб-SELECT в условии WHERE основного SELECT-а выполняется на каждом фетче. Хотя не должен, т.к. сам такой конструкцией пользуюсь повсеместно и странностей не замечал.
2)
А ты попробуй вариант, когда в основной несколько миллионов записей, а значений ключа, попадающих под условие поиска, - несколько тысяч (коих у тебя 55), причем выбрать их нужно из деревянного справочника с учетом вложенности (например, когда задается ID корневого элемента, а искать нужно во всех «подкаталогах»). И сравни скорости.
Спасибо за ответ) Кстати, по-моему, суб-SELECT действительно выполняется для каждого фетча. Даже с конструкцией in если я пишу SELECT … WHERE field IN (select…) выполняется медленнее, чем SELECT … WHERE field IN (список конкретных значений).
Просто я пытался выше написанным сделать, чтобы можно было выборку с произвольным набором значений осуществить таким образом: процедуркой сформировал строку '~1~2~…'. В приведенном примере получается, что процедурка для конкретного набора значений.
А так получается, что все равно в 2 этапа делать: формировать строку '~1~2~…', потом выполнять с ней запрос.
Вот сделал тестик только что: Длина строки '~1~2~…' 551 символ. В ней 87 различных значений №1 select sum(total) from any_table WHERE (select trim(in_sttmt) from build_in_statement('select unqfield from table_1 where code < 90')) containing '~'||unqfield||'~' /*Время выполнения 25.6с. Строка '~1~2~…' формируется процедуркой в запросе */
№2 select sum(total) from any_table where '~20~32~71…' containing '~'||unqfield||'~' /*23.8с, строка сформирована заранее (всю строку не приводил, она очень длинная) */
№3 select sum(total) from any_table where unqfield in (select unqfield from table_1 where code < 90) /*8.1с, обычное использование IN*/
№4 select sum(total) from any_table where unqfield in (20, 32, 71,…) /*1.2с, аргумент оператора IN сформирован заранее*/
№5 select sum(total) from any_table m, (select unqfield from table_1 where code < 90) in_list where m.unqfield = in_list.unqfield /*1.3с, вместо использования IN, суб-селект выступает как вторая таблица*/
№6 select sum(total) from any_table m, (select trim(in_sttmt) in_st from build_in_statement('select unqfield from table_1 where code < 90')) in_list where in_st containing '~'||unqfield||'~' /*24.7с, строка '~1~2~…' выступает как отдельная таблица (из одного значения)*/
Результат: -Между №1 и №2 разница небольшая. Значит, подзапрос выполняется все-таки не при каждом фетче здесь. -№3 с обычным использованием IN выполнился в 3 раза быстрее. Могу предположить, что использование '~1~2~…' всё же замедляет выполнение запроса. -№4 выполнился еще в 6 раз быстрее, чем №3. Видимо, в третьем тесте подзапрос выполнялся для каждой строки основного запроса. -№5 выполняется чуть медленнее, чем №4. Чем они отличаются написано в комментарии под запросом. -№6 можно сравнить с №2 аналогично 5 и 4.
Видим, что использование строки проигрывает использованию оператора in, а достойной альтернативой ему служит метод №5. Хотя возможно, в сложных выборках прийдется подумать как его применить.
Или я ошибся с выбором ситуаций для тестов?