Есть два запроса, которые возвращают одно и тоже, отличаются они только одной строчкой.



А вот этот запрос выполняется быстрее на порядок:


Цена первого запроса примерно 0.2, а второго запроса 0.01.
Вопрос: почему?

сделай explain запросам

в первом запросе не используется индекс 

если стоит вопрос оптимизации то лучше переделать IN на EXISTS, по слухам EXISTS быстрее, сам тестов не делал. Поправьте если не прав



Это сообщение отредактировал(а) Frees - 23.7.2010, 21:38

Что это значит? 

explain даст ответ на вопрос: "почему?"
vingrad: профилирование запросов в mysql 
mysql.com: explain syntax

sql-ex?
там вроде была педалька "показать план запроса".
План запроса это то, где Вы смотрите "цену".

На самом деле вопрос забавный.
Скорее всего предикат prd.model = prd.model вводит MS в заблуждение относительно того, что подзапрос кореллированный, в результате MS строит разные планы для этого запроса. Если это не баг, то, как минимум, недоделка оптимизатора.


Это сообщение отредактировал(а) Zloxa - 25.7.2010, 15:36

да, кстати, explain - конструкция для mysql
а с чего я взял, что речь идет о mysql?

Собсна вот планы



Если честно, даже после длительной медитации над этими запросами, я так и не понял, от чего в первом случае выполняетcя inner join, а не semi join, как во втором запросе, план которого мне кажется куда более разумным.
Однако ж косты у запросов отличаются отнюдь не на порядок, как то было обозначено ТС.


Это сообщение отредактировал(а) Zloxa - 25.7.2010, 17:19

удалил.


Это сообщение отредактировал(а) DimW - 26.7.2010, 08:24

Zloxa

На какой СУБД проводился опыт? Есть ли там возможность отключить оптимизацию для запроса? Случаи плохой оптимизации бывают, иногда отключение оптимизации на Oracle давало значительное повышение скорости работы запроса.

sql-ex крутится на ms sql 2003 или старше.... ибо конструкцию with держит 

у меня в свою очередь вопрос ТС
Откуда подчерпнут этот, на первый взгляд, нелепый трюк с предикатом "prd.model = prd.model"?

Да, я прошу прощения. Ошибся по поводу того, что различия в стоимостях на порядок.
Это действительно вопрос с сайта sql-ex.
Там при выполнении задания бывает ссылка слева появляется на обсуждение данного задания на форуме. Такой нелепый трюк с предикатом предложили именно там.
Если вы зарегены там, то вот ссылка.
Я там же спросил, дескать почему один запрос выполняется быстрее другого?
Местный гуру сказал, цитирую: "Такой предикат не ускоряет запрос, а изменяет стоимость плана."

А почему стоимость плана меняется в лучшую сторону?))) Я там просто дальше расспрашивать не стал, так как ответили мне неохотно)))

По ссылке все еще интереснее нежели Вы показали, ведь там предикат prd.model = prd.model находится за скобками подзапроса 


Он прав. Скорость выполнения запроса не есть стоимость плана. Стоимость запроса это некий относительный показатель каким то образом позволяющий оценить его эффективность. Стоимость запроса интересна прежде всего оптимизатору, из многих возможных планов, оптимизатор выбират тот, у кого меньшая стоиомсть. 

Я провел такой эксперимент:

В окне анализатора плана я ввел

получил cost:0.0033370000310242
EstimateRows: 5
аналогичный ему запрос

получил cost:0.0033370000310242
EstimateRows: 50

Чем обусловлена такая разница? Я полагаю тем, что оптимизатор не нашел ресурса оптимизиации предиката model=model(что он эквивалентен model is not null) и оценил его так же как оценивал бы model=:some_val. Очевидно оптимизатор владеет какойто информацией об  содержимом столбца model и считает что селективность этого предиката будет 0,1

теперь смотрим на ожидания по предикату pс.speed>=450 EstimateRows: 15.23076915741

Чтоже получается?
Получается что добавив этот предикат мы запутали оптимизатор и он решил что мы будем объединять не 15*50 строк, а 5*15. Естественно, что  для такого объединения будет рассчитана меньшая стоимость. 

Самое же интересное в этом случае, с моей точки зрения, то, что обманув оптимизатор, заставив его выдать некошерный план, мы еще и добились его улучшения. Почему оптимизатор выбирает изначально не верный план - не знаю/*и действительно ли он неверный*/. В обоих случаях лидирующим в  nested loops был выбран тот  набор, который имеет меньше строк. В первом случае это сделало невозможным выполнение semi join, и сделало необходимым введение дополнительной аггрегации для обеспечения distinct. Однако, т.к. в ведомом наборе у нас производится полное сканирование а не доступ по индексу, по идее смена лидирующей таблицы не должна бы влиять на стоимость запроса. Какая разница 15*50 или же 50*15? Получается либо у нас тут разночтения с оптимизатором и он действительно небезосновательно думает что не все равно какой набор данным будет тут лидирущим, даже не смотря на оверхед надстроенный сверзу- тогда я не прав. Либо же оптимизатор просто тупо ставит лидирущим набором тот, где меньше строк.

Это сообщение отредактировал(а) Zloxa - 27.7.2010, 12:50