Таблица SQL 2005 CTE vs TEMP Производительность при использовании в объединениях других таблиц

У меня есть сложный запрос, который мне нужно использовать в следующем запросе (фактически обновить выражение). Я попытался использовать CTE и временную таблицу. Производительность с использованием CTE ужасна по сравнению с подходом таблицы temp. Это что-то вроде 15 секунд против миллисекунд. Чтобы упростить тест вместо присоединения к таблице CTE/Temp в последующем запросе, я просто выбрал * из него. В этом случае они выполняют то же самое.

Я рассмотрел план выполнения для обоих подходов как с объединениями в последующем запросе, так и просто выберите *. При простом выборе планы запросов примерно одинаковы, но с присоединениями в последующем выборе планов запроса нет. В частности, часть плана запроса для создания и заполнения таблицы temp остается прежней, в то время как часть плана запроса для создания и заполнения CTE резко меняется, когда она впоследствии используется в запросе с соединением.

Мой вопрос: почему план запроса для создания и совокупности CTE изменяется по тому, как он впоследствии используется во временной таблице temp. Кроме того, в каких сценариях CTE дает лучшую производительность, чем временная таблица?

* Примечание. Я также использовал переменную таблицы и сопоставим с подходом таблицы temp.

Спасибо

Ответ 1

Вы задаете сложный вопрос, так что вы получаете сложный ответ: это зависит. (Я ненавижу этот ответ).

Серьезно, однако, это связано с тем, как оптимизатор выбирает план данных (который вы уже знали); временная таблица или переменная похожа на постоянную структуру, поскольку план выполнения будет выполнять операцию, связанную с заполнением этой структуры, а затем использовать эту структуру в последующих операциях. CTE не является временной таблицей; использование CTE не рассчитывается до тех пор, пока оно не будет использовано для последующих операций, и поэтому использование влияет на оптимизацию плана.

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

Ответ 2

CTE - это просто псевдоним для запроса.

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

Нет чистого способа принудить материализацию CTE в SQL Server (например, Oracle /*+ MATERIALIZE */), и вы должны делать такие грязные трюки, как это:

Генерация XML в подзапросах

CTE может повысить производительность, если используется в планах, требующих только одной оценки (например, HASH JOIN, MERGE JOIN и т.д.).

В этих сценариях хеш-таблица будет построена прямо из CTE, а при использовании таблицы temp потребуется оценить CTE, потянув результаты в таблицу temp и снова прочитав таблицу temp.

Ответ 3

Я нахожу, что обычно повторный CTE не улучшает производительность.

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

Вместо CTE я часто использую встроенные TVF (которые могут содержать CTE), что позволяет правильно повторное использование и не лучше или хуже, чем CTE в моих SP.

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

В этом случае я смотрю на ручное хранение промежуточных результатов, гарантируя их правильную индексацию и разделение процесса на несколько SP и добавление WITH RECOMPILE, чтобы гарантировать, что у более поздних SP есть планы, которые хороши для данных, которые они фактически будет работать.

Ответ 4

Я пробовал создавать CTE с простым выбором с фильтром из большой таблицы Затем 3 раза подчинил его.

После этого сделайте то же самое с временными таблицами.

Результат составлял 70% времени для CTE -30% времени, затрачиваемого на временную таблицу. Так что темп-таблица лучше для этих решений.

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