В чем разница между CTE и Temp Table?

В чем разница между общим выражением таблицы (CTE) и временной таблицей? И когда я должен использовать один над другим?

CTE

WITH cte (Столбец1, Столбец2, Столбец3)
В ВИДЕ
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

Таблица Temp

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
157 голосов | спросил Rachel 15 FebruaryEurope/MoscowbWed, 15 Feb 2012 20:47:27 +0400000000pmWed, 15 Feb 2012 20:47:27 +040012 2012, 20:47:27

7 ответов


185

Это довольно широко, но я дам вам как общий ответ, как только смогу.

CTE ...

  • Неинтенсивны (но могут использовать существующие индексы для ссылочных объектов)
  • Не может быть ограничений
  • В основном одноразовые VIEW s
  • Сохранять только до следующего запуска запроса.
  • Может быть рекурсивным
  • Не имеют выделенной статистики (полагайтесь на статистику по базовым объектам)

#Temp Таблицы ...

  • Реальные материализованные таблицы, которые существуют в tempdb
  • Может быть проиндексирован
  • Может иметь ограничения
  • Устойчивость к жизни текущего СОЕДИНЕНИЯ
  • Можно ссылаться на другие запросы или подпроцедуры
  • Выделите статистику, созданную двигателем.

Что касается использования каждого из них, они имеют очень разные варианты использования. Если у вас будет очень большой набор результатов или вам нужно будет обращаться к нему более одного раза, поместите его в таблицу #temp. Если он должен быть рекурсивным, является одноразовым или просто упрощает что-то логически, предпочтительнее CTE.

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

ответил JNK 15 FebruaryEurope/MoscowbWed, 15 Feb 2012 20:54:42 +0400000000pmWed, 15 Feb 2012 20:54:42 +040012 2012, 20:54:42
25

EDIT:

См. комментарии Мартина ниже:

  

CTE не является материализованным как таблица в памяти. Это всего лишь способ инкапсуляции определения запроса. В случае OP он будет встроен и будет таким же, как и выполнение SELECT Column1, Column2, Column3 FROM SomeTable. Большую часть времени они не получают материализованного фронта, поэтому это не возвращает строки WITH T (X) AS (SELECT NEWID ()) SELECT * FROM T T1 JOIN T T2 ON T1.X = T2.X, также проверьте планы выполнения. Хотя иногда можно взломать план, чтобы получить катушку. Для этого есть элемент подключения, запрашивающий подсказку. â € "Мартин Смит 15/02/12 в 17:08


Оригинальный ответ

CTE

Подробнее о MSDN

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

Вы также можете рассмотреть возможность использования переменной таблицы. Это используется как используется временная таблица и может использоваться несколько раз без необходимости повторной материализации для каждого соединения. Кроме того, если вам нужно сохранить несколько записей сейчас, добавьте еще несколько записей после следующего выбора, добавьте еще несколько записей после другого op, а затем верните только эти несколько записей, тогда это может быть удобной структурой, так как она не работает После выполнения нужно отказаться. В основном просто синтаксический сахар. Однако, если вы держите число строк ниже, оно никогда не материализуется на диск. См. В чем разница между таблицей temp и табличной переменной в SQL Server? для более подробной информации.

Таблица темпов

Подробнее о MSDN - прокрутите вниз примерно на 40% пути

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

Временные таблицы бывают двух видов: локальные и глобальные. В терминах MS Sql Server вы используете обозначение #tableName для локального и ## tableName для глобального (обратите внимание на использование одного или двойного # в качестве идентифицирующего признака ).

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


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

ответил jcolebrand 15 FebruaryEurope/MoscowbWed, 15 Feb 2012 20:52:28 +0400000000pmWed, 15 Feb 2012 20:52:28 +040012 2012, 20:52:28
12

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

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

Табличные переменные IIRC (с другой стороны) всегда являются структурами в памяти.

ответил ConcernedOfTunbridgeWells 15 FebruaryEurope/MoscowbWed, 15 Feb 2012 20:53:01 +0400000000pmWed, 15 Feb 2012 20:53:01 +040012 2012, 20:53:01
12

В принятом ответе здесь говорится: «CTE никогда не следует использовать для повышения производительности», но это может ввести в заблуждение. В контексте CTE по сравнению с временными таблицами я только что закончил удаление сальникового мусора из набора хранимых процедур, потому что некоторые doofus, должно быть, считали, что для использования временных таблиц мало или вообще не накладные расходы. Я запустил партию в CTE, кроме тех, которые были законным образом использованы повторно в течение всего процесса. Я получил около 20% производительности по всем показателям. Затем я решил удалить все курсоры, которые пытались реализовать рекурсивную обработку. Именно там я увидел наибольшую выгоду. В итоге я сократил время отклика в десять раз.

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

ответил Mel Padden 17 FebruaryEurope/MoscowbFri, 17 Feb 2012 13:49:37 +0400000000pmFri, 17 Feb 2012 13:49:37 +040012 2012, 13:49:37
10

Таблица Temp - это реальный объект в tempdb, но cte - это всего лишь оболочка сложного запроса, чтобы упростить синтаксис организации рекурсии за один шаг.

ответил Oleg Dok 15 FebruaryEurope/MoscowbWed, 15 Feb 2012 20:53:04 +0400000000pmWed, 15 Feb 2012 20:53:04 +040012 2012, 20:53:04
6

Основной причиной использования CTE является доступ к таким функциям окна, как row_number () и другие.

Это означает, что вы можете делать что-то вроде первой или последней строки в группе ОЧЕНЬ ОЧЕНЬ быстро и эффективно - ответил Dave Hilditch 8 +04002013-10-08T04:50:20+04:00312013bEurope/MoscowTue, 08 Oct 2013 04:50:20 +0400 2013, 04:50:20

5

Здесь, похоже, есть немного негатива в отношении CTE.

Мое понимание CTE заключается в том, что это в основном своего рода adhoc-представление. SQL - это декларативный и основанный на наборе язык. CTE - отличный способ объявить набор! Невозможность индексировать CTE на самом деле хорошо, потому что вам не нужно! Это действительно своего рода синтаксический сахар, чтобы облегчить чтение и запись запроса. Любой достойный оптимизатор разработает лучший план доступа с использованием индексов в базовых таблицах. Это означает, что вы могли бы эффективно ускорить ваш запрос CTE, следуя рекомендациям индекса в базовых таблицах.

Кроме того, только потому, что вы определили набор как CTE, это не означает, что все строки в наборе должны обрабатываться. В зависимости от запроса оптимизатор может обрабатывать строки «достаточно», чтобы удовлетворить запрос. Возможно, вам нужны только первые 20 или около того для вашего экрана. Если вы создали временную таблицу, вам действительно нужно прочитать /записать все эти строки!

Основываясь на этом, я бы сказал, что CTE - отличная функция SQL и может использоваться везде, где они упрощают чтение запроса. Я бы подумал только о временной таблице для пакетного процесса, который действительно должен обрабатывать каждую запись. Даже тогда afaik это не рекомендуется, потому что на временной таблице для базы данных намного сложнее помочь вам с кешированием и индексами. Возможно, было бы лучше иметь постоянную таблицу с полем PK, уникальным для вашей транзакции.

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

ответил Ben Thurley 25 MaramTue, 25 Mar 2014 02:30:03 +04002014-03-25T02:30:03+04:0002 2014, 02:30:03

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132