Почему существует огромная разница в производительности между временной таблицей и подзапросом

Это вопрос о SQL Server 2008 R2

Я не являюсь администратором баз данных. Я разработчик Java, который должен время от времени писать SQL. (в основном встроенный в код). Я хочу знать, сделал ли я что-то неправильно здесь, и если да, то что я могу сделать, чтобы это не повторилось.

Q1:

SELECT something FROM (SELECT * FROM T1 WHERE condition1) JOIN ...

Q1 имеет 14 соединений

Q2 - это то же самое, что и Q1, за одним исключением. (SELECT * FROM T1 WHERE condition1) выполняется до и хранится в таблице temp.

Это не коррелированный подзапрос.

2:

SELECT * INTO #tempTable FROM T1 WHERE condition1
SELECT something FROM #tempTable  JOIN ...

снова, 14 соединений.

То, что меня озадачивает сейчас, - это то, что Q1 взял > 2min (несколько раз попробовал его, чтобы избежать кеширования, чтобы играть роль), а Q2 (оба вместе взятых) заняли 2 секунды!!! Что дает?

Ответ 1

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

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

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

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

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

Ответ 2

Здесь можно найти много вещей, индексы, планы выполнения и т.д. Тестирование и сравнение результатов - это путь.

Вы можете взглянуть на обычных подозреваемых, индексов. Взгляните на план выполнения и сравните их. Убедитесь, что в предложении WHERE используются правильные. Убедитесь, что вы используете индексы на JOINs.

Эти ответы наверняка помогут вам.