Какие более производительные, CTE или временные таблицы?

Я бы сказал, что это разные концепции, но не слишком разные, чтобы сказать «мел и сыр».

• Временная таблица хороша для повторного использования или для выполнения нескольких проходов обработки набора данных.

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

• Временная таблица - это еще одна таблица с некоторыми правилами в отношении области видимости.

Я сохранил процессы, в которых я использую оба (и переменные таблицы тоже)

По-разному.

Прежде всего

Что такое обычное табличное выражение?

(Нерекурсивный) CTE обрабатывается очень аналогично другим конструкциям, которые также могут использоваться в качестве встроенных табличных выражений в SQL Server. Производные таблицы, представления и встроенные табличные функции. Обратите внимание, что хотя в BOL сказано, что CTE можно рассматривать как временный набор результатов, это чисто логическое описание. Чаще всего он не материализован сам по себе.

Что такое временная таблица?

Это набор строк, хранящихся на страницах данных в базе данных tempdb. Страницы данных могут частично или полностью находиться в памяти. Кроме того, временная таблица может быть проиндексирована и иметь статистику по столбцам.

Тестовые данные

CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO T(B)
SELECT TOP (1000000)  0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
     master..spt_values v2;

Пример 1

WITH CTE1 AS
(
SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780

Обратите внимание, что в плане выше нет упоминания CTE1. Он просто обращается напрямую к базовым таблицам и обрабатывается так же, как

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM   T
WHERE  A = 780 

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

Материализация определения CTE

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T

Потребуется копирование около 8 ГБ данных во временную таблицу, тогда еще есть накладные расходы на выбор из нее.

Пример 2

WITH CTE2
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY A) AS RN
         FROM   T
         WHERE  B % 100000 = 0)
SELECT *
FROM   CTE2 T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   CTE2 T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

Приведенный выше пример на моей машине занимает около 4 минут.

Только 15 строк из 1000000 случайно сгенерированных значений соответствуют предикату, но для их обнаружения требуется 16 раз дорогостоящее сканирование таблицы.

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

INSERT INTO #T
SELECT *,
       ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM   T
WHERE  B % 100000 = 0

SELECT *
FROM   #T T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   #T T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

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

В некоторых случаях SQL Server будет использовать спул для кэширования промежуточного результата, например CTE и избегайте повторной оценки этого поддерева. Это обсуждается в (перенесенном) элементе Connect Предоставьте подсказку для принудительной промежуточной материализации CTE или производных таблиц. Однако статистика по этому поводу не создается, и даже если количество буферизованных строк должно сильно отличаться от расчетного, план выполнения не может динамически адаптироваться в ответ (по крайней мере, в текущих версиях. Адаптивные планы запросов могут стать возможными в будущее).

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

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

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

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

Итак, в итоге нет четкого ответа, но лично я предпочел бы CTE временным таблицам.

Итак, запрос, который мне поручили оптимизировать, был написан с двумя CTE на сервере SQL. Прошло 28 секунд.

Я потратил две минуты на преобразование их во временные таблицы, а запрос занял 3 секунды.

Я добавил индекс в временную таблицу в поле, к которому он был присоединен, и уменьшил его до 2 секунд.

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

Самое безумное, что CTE использовались только один раз, и индексирование их оказалось на 50% быстрее.

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

Например, я создал sprocs, которые возвращают результаты больших вычислений за 15 секунд, но конвертируют этот код для работы в CTE, и видел, как он работал более 8 минут для достижения тех же результатов.

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

Временные таблицы временные. Мы можем создавать индексы, ограничения, как обычные таблицы, для чего нам нужно определить все переменные.

Объем временной таблицы только в рамках сеанса. EX: открыть два окна SQL-запроса.

create table #temp(empid int,empname varchar)
insert into #temp 
select 101,'xxx'

select * from #temp

Запустите этот запрос в первом окне, затем запустите приведенный ниже запрос во втором окне, вы можете найти разницу.

select * from #temp

Поздно на вечеринку, но ...

Среда, в которой я работаю, очень ограничена, поддерживая некоторые продукты поставщиков и предоставляя дополнительные услуги, такие как отчетность. Из-за ограничений политики и контракта мне обычно не разрешается роскошь отдельной таблицы / пространства данных и / или возможность создавать постоянный код [это становится немного лучше, в зависимости от приложения].

IOW, я не могу обычно разрабатывать хранимую процедуру или UDF или временные таблицы и т. Д. Мне в значительной степени приходится делать все через интерфейс МОЕГО приложения (Crystal Reports - таблицы добавления / связывания, установка предложений where из ж / в ЧР и др.). Одна НЕБОЛЬШАЯ изюминка заключается в том, что Crystal позволяет мне использовать КОМАНДЫ (а также выражения SQL). Некоторые вещи, которые неэффективны при использовании обычных возможностей добавления / связывания таблиц, можно сделать, определив команду SQL. Я использую CTE через это и получил очень хорошие результаты "удаленно". CTE также помогают с обслуживанием отчетов, не требуя разработки кода, передачи его администратору баз данных для компиляции, шифрования, передачи, установки и последующего многоуровневого тестирования. Я могу выполнять CTE через локальный интерфейс.

Обратной стороной использования CTE с CR является то, что каждый отчет является отдельным. Каждый CTE должен поддерживаться для каждого отчета. Там, где я могу выполнять SP и UDF, я могу разработать что-то, что может использоваться в нескольких отчетах, требуя только ссылки на SP и передачи параметров, как если бы вы работали с обычной таблицей. CR не очень хорошо справляется с обработкой параметров в командах SQL, поэтому этот аспект аспекта CR / CTE может отсутствовать. В таких случаях я обычно пытаюсь определить CTE для возврата достаточного количества данных (но не ВСЕХ данных), а затем использую возможности выбора записей в CR, чтобы разрезать их и нарезать кубиками.

Итак ... я голосую за CTE (пока я не получу свое пространство данных).

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

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

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

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

Традиционная временная таблица хранит данные во временной базе данных, что действительно замедляет работу временных таблиц; однако табличных переменных нет.

Я только что протестировал это - и CTE, и не-CTE (где запрос вводился для каждого экземпляра объединения) занимали ~ 31 секунду. CTE сделал код более читабельным - сократил его с 241 до 130 строк, что очень приятно. Таблица Temp, с другой стороны, сократила его до 132 строк, и на выполнение потребовалось ПЯТЬ СЕКУНД. Без шуток. все это тестирование было кэшировано - все запросы до этого выполнялись несколько раз.

Исходя из моего опыта работы с SQL Server, я нашел один из сценариев, в котором CTE превосходит таблицу Temp.

Мне нужно было использовать DataSet (~ 100000) из сложного запроса только ОДИН РАЗ в моей хранимой процедуре.

• Таблица Temp вызвала накладные расходы на SQL, где моя процедура выполнялась медленно (поскольку таблицы Temp - это реальные материализованные таблицы, которые существуют в tempdb и Persist на протяжении всей моей текущей процедуры)

• С другой стороны, с CTE CTE сохраняется только до тех пор, пока не будет запущен следующий запрос. Итак, CTE - это удобная структура в памяти с ограниченной областью действия. CTE по умолчанию не используют tempdb.

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

WITH CTE1(ID, Name, Display) 
AS (SELECT ID,Name,Display from Table1 where <Some Condition>),
CTE2(ID,Name,<col3>) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 <Some Condition>)
SELECT CTE2.ID,CTE2.<col3>
FROM CTE2
GO