Вложенные запросы

← →
Piter © (2004-08-21 00:02) [0]

Вот читаю тут литературу. Пишут, что вложенные подзапросы оформляются так:

SELECT * FROM Orders WHERE snum = ( SELECT snum FROM Salespeople WHERE city = "Москва" )

При этом если подзапрос возвращает несколько значений, то писать вроде как нужно так:

SELECT * FROM Orders WHERE snum IN ( SELECT snum FROM Salespeople WHERE city = "Москва" )

То есть, заменять "=" на "IN". Но когда я так делаю в программе - то сервер начинает жутко тормозить, в конце концов все выполняет правильно, но делает это больше минуты! (на базе в 7Mb).

Если же писать все таки "=", то не смотря на то, что подзапрос возвращает несколько строк - все проходит опять же на ура, но ГОРАЗДО быстрее (меньше секунды)...

Ничего не понимаю! Что же происходит?

P.S. Эксперименты проводил на различных Firebird серверах

← →
P.N.P. © (2004-08-21 00:34) [1]

Что-то Вы путаете...
Если писать "=" и подзапрос возвращает несколько строк, то
сервер будет ругаться
multiple rows in singleton select

← →
Piter © (2004-08-21 00:54) [2]

P.N.P. © (21.08.04 0:34) [1]

в том то и дело, что нет.

Если писать IN, то сервер обрабатывает запрос около минуты!!! Это при том, что внутрений запрос сам по себе отрабатывает меньше секунды! И еще не возвращает ничего (условия не выполнены). И все равно сложный запрос работает минуту! Вот что странно...

А при использовании "=" - все ок и очень быстро. Я целый день с этим промучился...

← →
Piter © (2004-08-21 00:55) [3]

У меня есть мысль, что поведение Firebird и MSSQL в этом деле различаются...

В общем, неплохо, чтобы кто-нибудь знающий пояснил...

← →
P.N.P. © (2004-08-21 01:57) [4]

http://www.krista.ru/ib/subq-opt.html#SECTION00033000000000000000

← →
Piter © (2004-08-21 12:40) [5]

Так, правильно я понимаю, что для конструкции с "=" будет сначала выполнен внутренний запрос, а потом на основе его данных внешний? А если поставить IN, то сначала будет выполнен внешний запрос, каждый раз вызывая внутренний?

Или как?

← →
P.N.P. © (2004-08-21 12:44) [6]

именно так.

← →
Piter © (2004-08-21 13:40) [7]

Моя не понимать... а нафига такое сделано? Кто такие подзапросы делает??! На моей малюсенькой базе в 7 Mb он выполняется минуту!!! При этом ничего не возвращая.

То есть, если отдельно сделать внутренний запрос, то он ничего не вернет! При этом он вызывается для каждой записи внешнего запроса! ГЛУПО!

И что, собственно, теперь делать?

Мне в общем, нужна реализация случая, когда ставится "=", но чтобы он работал в случаях, когда внутренний запрос возвращает несколько записей!

Или я уже подумываю, чтобы вручную сделать внутренний подзапрос и сохранить все выданные snum, а потом сделать n-ое количество внешних запросов с этими полученными snum...

Но как-то опять же глупо получается...

P.S. А когда этот IN применяется то? Ведь если он выполняется для КАЖДОЙ записи таблицы из внешнего запроса? На кой он нужен? Тормознее его, наверное, ничего нету...

← →
Nikolay M. © (2004-08-21 13:46) [8]

А если так? Должно быть быстро

SELECT *
FROM Orders o
WHERE EXISTS (SELECT 1
FROM Salespeople s
WHERE s.city = "Москва" AND s.snum = o.snum)

← →
Sandman25 © (2004-08-21 14:06) [9]

Если возвращается несколько одинаковых записей, то для IB стоит писать
= (select distinct ...)

← →
Piter © (2004-08-21 14:14) [10]

Sandman25 © (21.08.04 14:06) [9]

Нет, записи разные, но их несколько

← →
Sandman25 © (2004-08-21 14:22) [11]

Тогда только IN или Join.

← →
Piter © (2004-08-21 14:55) [12]

а объясните - зачем нужен IN?!

Даже если в базе хотя бы пара тысяч записей - наблюдаются нефиговые тормоза. Да и алгоритм явно тупиковый - вот внутренний запрос вообще не возвращает значений, по идее внешний тогда тоже не может вернуть ничего. Это очевидно.

А FB над этим думает минуту...

← →
Sandman25 © (2004-08-21 14:58) [13]

>а объясните - зачем нужен IN?!

Все нормальные СУБД типа MSSQL, Oracle или Informix не тормозят в данном случае.

← →
-SeM- (2004-08-21 15:14) [14]

Давайте только не будет делить на нормальные и нет.
Ненормальным может быть подход. Какой план запроса?
Осмелюсь еще раз отправить [4]

← →
Piter © (2004-08-21 15:28) [15]

Nikolay M. © (21.08.04 13:46) [8]
А если так? Должно быть быстро

нет, ничего не меняется... правда, у меня ситуация посложнее, чем на примере. Типа того:

SELECT * FROM Post WHERE Text like :p3 AND EXISTS ( SELECT 1 FROM Tree WHERE Upper(Tree.Title COLLATE PXW_CYRL) like :p1 AND Tree.ID=Post.ID )

Даже если внутренний запрос пустой, даже если при этом и внешний запрос будет пустой при отсутствии EXISTS - все равно думает минуту :( Впрочем, думает ту же самую минуту и при наличии записей.

Фигня какая-то...

← →
Sandman25 © (2004-08-21 15:33) [16]

[14] -SeM- (21.08.04 15:14)

Хорошо, делить СУБД не будем. Но поведение оптимизатора-вычислителя ненормально.

← →
Desdechado © (2004-08-21 15:34) [17]

Конструкция
Upper(Tree.Title COLLATE PXW_CYRL) like :p1
вообще критики не выдерживает
А у тебя еще и кореллированный подзапрос, а это значит, что для его выполнения нужны данные из КАЖДОЙ строки главного запроса.
Попробуй зайти с другой стороны, измени влогику. Может, через объединения.

← →
-SeM- (2004-08-21 15:38) [18]

Piter © (21.08.04 15:28) [15]

> Даже если внутренний запрос пустой, даже если при этом и
> внешний запрос будет пустой при отсутствии EXISTS - все
> равно думает минуту :( Впрочем, думает ту же самую минуту
> и при наличии записей

То что он пустой - не означает что ничего не делается. И минуту он не думает, а перебирает записи согласно like.
Еще раз, какой план запроса?

← →
Desdechado © (2004-08-21 15:39) [19]

SELECT DISTINCT P.* FROM Post P, Tree T WHERE ( P.text like :p3 ) AND ( Upper( T.title COLLATE PXW_CYRL ) like :p1 ) AND T.ID = P.ID

← →
-SeM- (2004-08-21 15:44) [20]

Sandman25 © (21.08.04 15:33) [16]

1. Не нормальний текст запроса [1]
2. Видим различие в примере и в [15]

Отсюда и результат

Desdechado © (21.08.04 15:34) [17]
Конструкция
Upper(Tree.Title COLLATE PXW_CYRL) like :p1
вообще критики не выдерживает

а можно узнать почему? Имъо, стандартная структура для поиска по текстовому полю без учета регистра букв! Что тут плохого?!

-SeM- (21.08.04 15:38) [18]
То что он пустой - не означает что ничего не делается. И минуту он не думает, а перебирает записи согласно like

я уже говорил, что внутренний запрос сам по себе выполняется меньше секунды... не знаю чего он там перебирает...

Еще раз, какой план запроса?

А как это посмотреть? Я не сталкивался раньше с планами :(

Написал такое:

SELECT *
FROM Post
WHERE Text like "%ggggggg" AND EXISTS (
SELECT 1
FROM Tree
WHERE Upper(Tree.Title COLLATE PXW_CYRL) like "aaaaaaa"
AND Tree.ID=Post.ID )

PLAN (Tree INDEX (RDB$PRIMARY2))
PLAN (Post NATURAL)

но пишется, что динамическая ошибка SQL - не знает что такое PLAN

Desdechado © (21.08.04 15:34) [17]
что для его выполнения нужны данные из КАЖДОЙ

зачем? Я не понимаю... Лично я вижу два варианта реализации:

1) выполнить внешний запрос без внутреннего. А потом отфильтровать в соответствии с внутренним запросом

2) выполнить внутренний запрос. А потом сделать внешний с учетом полученных значений внутреннего.

Эффективность того или иного способа зависит от того, кто больше записей отфильтрует. То есть, по идее лучшим вариантом является тот, который при первом запросе получит наименьшее количество записей, чтобы легче было накладывать ограничения второго запроса.

Но реализовано совсем по другому, напрямик и глупо!

В любом случае, яделал пример, когда и внешний и внутренний запрос вернут всего лишь одну запись! То есть, сам по себе внутренний запрос возвращает одну запись. Внешний без внутреннего тоже возвращает одну запись!!! А когда их объединили - начинается просчитываться КАЖДАЯ строчка таблицы, которую используешь внешний запрос. На мой взгляд это настолько тупо, что я даже не знаю зачем такое сделано вообще!

Desdechado © (21.08.04 15:39) [19]

работает на УРА! Спасибо!

А можно объяснить, что было не так и как ты своим запросом этого избежал... я никак не могу догнать...

>cтандартная структура для поиска по текстовому полю
только в очень редких случаях применяемая из-за нечеткости поиска
и функции, что блокирует использование индексов

>Лично я вижу два варианта реализации
Сервер видит другие :) Потому что у тебя каждая запись содержит разные значения, ограничивающие поиск в подзапросе. А сервер не знает о них, пока не выполнит запрос. Но при выполнении запроса у тебя стоит условие проверки их наличия. Т.о., чтобы разрулить ситуацию, серверу надо на каждую строку выполнить подзапрос с параметром.

>А можно объяснить
Практически любой кореллированный подзапрос можно переписать с использованием объединения таблиц. Обычно это эффективнее, хотя бывают исключения. Ну, а IB с подзапросами работает не на лучшем уровне. Поэому их я почти неиспользую.

← →
-SeM- (2004-08-21 16:57) [24]

Piter © (21.08.04 16:23) [22]

> А как это посмотреть? Я не сталкивался раньше с планами :(
Ну, например выполнить этот запрос в IBExpert, после чего увидишь используемый план запроса, по которому видно какие индексы используются сервером для выборки.

>А можно объяснить
У тебя есть две таблицы, объедененные одним полем.
В твоей реализации оптимизатор из-за двух селектов пробегал по двум таблицам поочередно, на каждую накладывая ограничение.
В [19] же сервер сначала объеденяет две таблицы, а уж потом накладывает ограничения. Может также использовать объединение индексов этих таблиц, что еще больше ведет к увиличению производительности.

[24] -SeM- (21.08.04 16:57)
В [19] же сервер сначала объеденяет две таблицы, а уж потом накладывает ограничения.

Скорее наоборот, объединяет уже отфильтрованные таблицы. Поэтому и выигрыш.

← →
-SeM- (2004-08-21 17:05) [26]

Desdechado © (21.08.04 16:36) [23]
только в очень редких случаях применяемая из-за нечеткости поиска
и функции, что блокирует использование индексов

Ну а как тогда искать по русски без учета регистра? Расскажи!

Насколько я понимаю, здесь отношение
один salespeople-ко-многим orders
и в данном случае связь установлена по полю snum, которое, надеюсь, имеет подобие индекса. Это задача для простого внутреннего объединения и делать ее через подзапросы не верно.
Почему бы банально не написать
select * from orders o, salespeople s where o.snum = s.snum and s.city = "Москва"
?
ИМХО, в данном случае это то же самое. И сработает мгновенно, если, конечно есть еще и индекс city. Хотя для city здесь явно отдельная справочная таблица напрашивается. Но это так, к слову.

Вложенные запросы Найти похожие ветки