Если требуется отобрать записи из таблицы, одно из полей которых FIELD1 должно принадлежать заданному множеству значений, то лучше не использовать конструкцию IN в условии WHERE, т.к. парсер запросов внутри себя превращает ее в конструкцию OR(FIELD1 = <…>)OR(FIELD1 = <…>)OR(FIELD1 = <…>)……….OR(FIELD1 = <…>).
Гораздо эффективней по скорости выборки данных способ предложенный Ded-ом:
Например:
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
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
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. Хотя возможно, в сложных выборках прийдется подумать как его применить.
Или я ошибся с выбором ситуаций для тестов?