«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу

select * from a, b, c where a.id = b.id, b.id = c.id

и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично

select * from a join b on a.id = b.id join c on b.id = c.id

Встроенный оптимизатор причешет быдлозапрос и все будет окей.

Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.

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

Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join  будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.

Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.

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

Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.

select * from a where a.id in (select id from b)

select * from a where exists (select top 1 1 from b where b.id = a.id)

select * from a join b on a.id = b.id

select * from a where a.id not in (select id from b)

select * from a where not exists (select top 1 1 from b where b.id = a.id)

select * from a left join b on a.id = b.id where b.id is null

Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.

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

Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.

select d.PRODUCT_ID
from PRODUCT s, PRODUCT_GROUP sg
left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID),
PRODUCT d, PRODUCT_GROUP dg
left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID)
where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID
and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID
and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC
and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME
and s.PRODUCT_NAME=d.PRODUCT_NAME
and s.PRODUCT_TYPE=d.PRODUCT_TYPE
and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE
and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT
and dg.PRODUCT_GROUP_IS_TMPL=0
and (
(
	    sd.M_PG_DEPENDENCY_CHILD_ID is null
	    and
	    dd.M_PG_DEPENDENCY_CHILD_ID is null
	  )
	  or exists
	  (
		select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1
		 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and
		       dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and
		       sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and
		       sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and
	  )
	)

Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.

select d.PRODUCT_ID
from PRODUCT s
join PRODUCT d on
    s.PRODUCT_TYPE=d.PRODUCT_TYPE
    and s.PRODUCT_NAME=d.PRODUCT_NAME
    and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE
    and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT
join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID
join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID
    and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME
    and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC
left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID
left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID
left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID
left join PRODUCT_GROUP dgp on
    dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID
    and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME
    and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0)
where
	  (
		sd.M_PG_DEPENDENCY_CHILD_ID is null
		and
		dd.M_PG_DEPENDENCY_CHILD_ID is null
	  )
	  or
	  (
		sgp.PRODUCT_GROUP_NAME is not null
		and
		dgp.PRODUCT_GROUP_NAME is not null
	  )
go

После данных преобразований производительность вьюхи увеличилась экспоненциально количеству найденных продуктов. Вернее сказать, время поиска оставалось практически независимым от числа совпадений и было всегда очень маленьким. Как и должно быть.

Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.