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

Ответ 1

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

Он может храниться в 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.

Ответ 2

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

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

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

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

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

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

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

Ответ 3

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

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

Ответ 4

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