Есть ли разница в производительности между CTE, подзапросом, временной таблицей или табличной переменной?
В этом вопросе были рассмотренны различия между CTE (common table expresion) и подзапросами.
Но хотелось бы конкретизировать вопрос. При каких обстоятельствах каждый из следующих подходов более производителен?
- CTE
- Подзапрос
- Временная таблица
- Табличная переменная
Часто пользовался хранимыми процедурами, так как они выглядят более читабельными, чем множество вложенных подзапросов. Не рекурсивные CTE очень хорошо инкапсулируют наборы данных и тоже очень удобочитаемы.
Но есть ли конкретные обстоятельства, когда можно сказать, что тот или иной подход всегда будет работать производительней? Или надо постоянно выбирать на ощупь из разных подходов, чтобы найти наиболее производительное решение?
PS
Недавно узнал, что с точки зрения производительности, временные таблицы являются хорошим первым выбором, поскольку они имеют связанную с ними статистику.
Свободный перевод Is there a performance difference between CTE, Sub-Query, Temporary Table or Table Variable? от участника @whytheq
Ответы (1 шт):
SQL является декларативным языком, а не процедурным языком. То есть, создается запрос SQL для описания желаемых результатов, но при этом движку SQL не указывается, как ему выполнить запрос.
Как правило, желательно, чтобы оптимизатор в движке SQL нашёл сам лучший план выполнения запроса. На разработку движка SQL уходит очень много человеко-лет, поэтому положитесь на многолетний опыт разработчиков, который они накопили выполняя эту работу.
Несомненно, бывают ситуации, когда план запроса не является оптимальным. В этом случае надлежит использовать хинты запроса, реструктурировать запрос, обновлять статистику, использовать временные таблицы, добавлять индексы и т.д., чтобы повысить производительность.
Теоретически производительность CTE и подзапросов должна быть одинаковой, поскольку оба предоставляют одинаковую информацию оптимизатору запросов. Одно из отличий состоит в том, что CTE, использованный более одного раза, можно легко идентифицировать и выполнить один раз, то есть результаты могут быть сохранены и прочитаны несколько раз. К сожалению это не всегда так. SQL Server, например, не использует похоже преимущества этого базового метода оптимизации (назовём это общим устранением подзапроса).
Временные таблицы - это другое дело, потому что имеется больше информации о том, как должен выполняться запрос. Одно из основных отличий заключается в том, что оптимизатор может использовать статистику из временной таблицы для составления плана запроса. Это может привести к повышению производительности. Кроме того, если у вас есть сложный CTE (или подзапрос), который используется более одного раза, то сохранение его во временной таблице часто дает повышение производительности. Запрос выполняется только один раз.
И собственно ответ на вопрос заключается в том, что нужно попробовать различные варианты запроса, чтобы получить ожидаемую производительность, особенно для сложных запросов, которые выполняются на регулярной основе. Идеально было бы, чтобы оптимизатор SQL нашёл бы идеальный путь выполнения. Хотя это часто так и есть, но всегда остаётся возможность найти способ повысить производительность.
Свободный перевод ответа от участника @GordonLinoff