Влияние на производительность прикованной таблицы CTE и Temp

У меня есть следующий цепной запрос CTE (упрощенно):

;WITH CTE1
AS(    
    SELECT * FROM TableA
),
CTE2
AS(
    SELECT * FROM TableB b INNER JOIN CTE1 c ON b.id = c.id
)

SELECT * FROM CTE2

Если я разорву цепочку CTE и сохраню данные CTE1 во временную таблицу, производительность всего запроса улучшится (с 1 минуты 20 секунд до 8 секунд).

;WITH CTE1
AS(    
    SELECT * FROM TableA
)

SELECT * INTO #Temp FROM CTE1

;WITH CTE2
AS(
    SELECT * FROM TableB b INNER JOIN #Temp c ON b.id = c.id
)

SELECT * FROM CTE2
DROP TABLE #Temp

Есть сложные запросы в CTE1 и CTE2. Я только что создал упрощенную версию, чтобы объяснить здесь.

Должен ли сломанный стул CTE улучшить производительность?

Версия SQL Server: 2008 R2

Источник

3 ответа

Решение

Очевидно, что может, как вы сами показали.

Зачем? Наиболее очевидная причина в том, что оптимизатор знает размер временной таблицы. Это дает ему больше информации для оптимизации запроса. CTE - только оценка. Итак, улучшение, которое вы видите, связано с планом запроса.

Другая причина может заключаться в том, что на запрос ссылается CTE несколько раз. SQL Server не материализует CTE, поэтому код определения будет выполняться несколько раз.

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

При всем этом я предпочитаю избегать временных таблиц. Оптимизатор обычно довольно хорош.

Источник

Считайте, что cte1 стоит дорого

;WITH CTE1
AS(    
    SELECT * FROM TableA
)

SELECT * INTO #Temp FROM CTE1

Выше гарантий cte1 запускается только один раз.

Связанный cte может оценивать cte1 несколько раз.

И даже с #temp вы должны рассмотреть index / PK и отсортировать вставку.

Источник

Это зависит от многих факторов. Всегда старайтесь написать единственное утверждение, если можете. Преждевременная оптимизация является корнем зла.

Если у вас возникли проблемы с производительностью, вот некоторые из преимуществ разложения вашего единственного утверждения:

  • Это может повысить ремонтопригодность, которая является одним из многих нефункциональных требований, за счет уменьшения сложности.
  • Это может дать лучший план, если стоимость промежуточной материализации и сэкономленное время меньше первоначальной стоимости.
  • Промежуточные таблицы могут быть проиндексированы.
  • Индексы, первичные ключи и уникальные ограничения очень полезны для оптимизатора не только для выбора типов соединений, но и для оценки количества элементов, что оказывает большое влияние на предоставление памяти.
  • Можно выбрать применение подсказок оптимизатора, таких как MAXDOP, только для выбора операторов, а не одного гигантского оператора. Это особенно полезно, когда вам нужно манипулировать предоставлением памяти.
  • Вы можете настроить отдельные операторы для устранения разлива в базу данных.
  • В зависимости от сложности и общего времени выполнения вашего процесса вы можете снять блокировку ресурсов раньше, в зависимости также от того, на каком уровне изоляции работают ваши операторы.
  • Если ваш план запросов плохой из-за тайм-аута оптимизатора, использование менее сложных отдельных операторов может привести к лучшим общим результатам.
Источник
Другие вопросы по тегам