Какой из них имеет лучшую производительность: производные таблицы или временные таблицы

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

4 ответа

Производная таблица является логической конструкцией.

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

Временная таблица - это физическая конструкция. Это таблица в tempdb, которая создается и заполняется значениями.

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

Например, CTE (общие табличные выражения) в SQL Server может (и, скорее всего, будет) пересматриваться каждый раз, когда они используются. Этот запрос:

WITH q (uuid) AS
 (
 SELECT NEWID()
 )
SELECT *
FROM q
UNION ALL
SELECT *
FROM q

скорее всего, даст два разных NEWID().

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

С другой стороны, этот запрос:

SELECT *
FROM (
 SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
 FROM master
 ) q
WHERE rn BETWEEN 80 AND 100

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


Это зависит от обстоятельств.

Преимущества производных таблиц:

  • Производная таблица является частью более крупного одиночного запроса и будет оптимизирована в контексте остальной части запроса. Это может быть преимуществом, если оптимизация запросов помогает повысить производительность (обычно это происходит, за некоторыми исключениями). Пример: если вы заполняете временную таблицу, затем потребляете результаты во втором запросе, вы фактически привязываете механизм базы данных к одному методу выполнения (сначала выполняйте первый запрос, сохраняйте весь результат, запустите второй запрос), где с производной таблицей оптимизатор может найти более быстрый способ выполнения или путь доступа.

  • Производная таблица только "существует" в терминах плана выполнения запроса - это чисто логическая конструкция. Там действительно нет таблицы.

Преимущества временных таблиц

  • Таблица "существует" - то есть она материализовалась как таблица, по крайней мере в памяти, которая содержит набор результатов и может быть повторно использована.

  • В некоторых случаях производительность может быть улучшена или заблокирована, если вам придется выполнить какое-то сложное преобразование данных - например, если вы хотите извлечь набор строк из базовой таблицы, занят, а затем выполняет некоторые сложные вычисления на этом наборе, может быть меньше конфликтов, если вы выберете строки из базовой таблицы и разблокируете ее как можно быстрее, а затем выполните работу самостоятельно. В некоторых случаях накладные расходы реальной таблицы temp малы относительно преимущества в concurrency.


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

Сегодня я посмотрел пример, который длился 10:15. Я ввел результаты из производной таблицы в временную таблицу и присоединился к таблице temp в основном запросе, а общее время снизилось до 0:03. Обычно, когда мы видим проблему с большой производительностью, мы можем быстро ее решить. По этой причине я рекомендую временные таблицы, если ваш запрос относительно прост, и вы уверены, что он не будет обрабатывать большие наборы данных.


Большая разница заключается в том, что вы можете помещать ограничения, включая первичный ключ во временную таблицу. Для больших (я имею в виду миллионы записей) когда-нибудь вы можете получить лучшую производительность с временными. У меня есть ключевой запрос, которому требуется 5 соединений (каждое объединение похоже на аналогичное). Производительность была в порядке с 2 присоединениями, а затем по третьему результату ухудшилась, и план запросов сошел с ума. Даже с подсказками я не мог исправить план запроса. Пробовал реструктуризацию объединений как производных таблиц и все те же проблемы с производительностью. С помощью временных таблиц можно создать первичный ключ (тогда, когда я заполню первую сортировку на ПК). Когда SQL мог бы присоединиться к 5 таблицам и использовать производительность ПК, от минут до нескольких секунд. Я бы хотел, чтобы SQL поддерживал ограничения на производные таблицы и CTE (даже если только PK).

licensed under cc by-sa 3.0 with attribution.