Почему CTE (Common Table Expressions) в некоторых случаях замедляет запросы по сравнению с временными таблицами в SQL Server
У меня есть несколько случаев, когда мой сложный CTE (Common Table Expressions) в десять раз медленнее, чем те же запросы, что и временные таблицы в SQL Server.
Мой вопрос здесь в том, как SQL Server обрабатывает запросы CTE, похоже, что он пытается объединить все отделенные запросы вместо хранения результатов каждого из них, а затем пытается запустить следующие. Таким образом, это может быть причиной, по которой это происходит быстрее при использовании временных таблиц.
Например:
Запрос 1: использование Common Table Expression:
;WITH Orders AS
(
SELECT
ma.MasterAccountId,
IIF(r.FinalisedDate IS NULL, 1, 0)) [Status]
FROM
MasterAccount ma
INNER JOIN
task.tblAccounts a ON a.AccountNumber = ma.TaskAccountId
AND a.IsActive = 1
LEFT OUTER JOIN
task.tblRequisitions r ON r.AccountNumber = a.AccountNumber
WHERE
ma.IsActive = 1
AND CAST(r.BatchDateTime AS DATE) BETWEEN @fromDate AND @toDate
AND r.BatchNumber > 0
),
StockAvailability AS
(
SELECT sa.AccountNumber,
sa.RequisitionNumber,
sa.RequisitionDate,
sa.Lines,
sa.HasStock,
sa.NoStock,
CASE WHEN sa.Lines = 0 THEN 'Empty'
WHEN sa.HasStock = 0 THEN 'None'
WHEN (sa.Lines > 0 AND sa.Lines > sa.HasStock) THEN 'Partial'
WHEN (sa.Lines > 0 AND sa.Lines <= sa.HasStock) THEN 'Full'
END AS [Status]
FROM
(
SELECT
r.AccountNumber,
r.RequisitionNumber,
r.RequisitionDate,
COUNT(rl.ProductNumber) Lines,
SUM(IIF(ISNULL(psoh.AvailableStock, 0) >= ISNULL(rl.Quantity, 0), 1, 0)) AS HasStock,
SUM(IIF(ISNULL(psoh.AvailableStock, 0) < ISNULL(rl.Quantity, 0), 1, 0)) AS NoStock
FROM task.tblrequisitions r
INNER JOIN task.tblRequisitionLines rl ON rl.RequisitionNumber = r.RequisitionNumber
LEFT JOIN ProductStockOnHandSummary psoh ON psoh.ProductNumber = rl.ProductNumber
WHERE dbo.fn_RemoveUnitPrefix(r.BatchNumber) = 0
AND r.UnitId = 1
AND r.FinalisedDate IS NULL
AND r.RequisitionStatus = 1
AND r.TransactionTypeNumber = 301
GROUP BY r.AccountNumber, r.RequisitionNumber, r.RequisitionDate
) AS sa
),
Available AS
(
SELECT ma.MasterAccountId,
SUM(IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END)) AS AvailableStock,
SUM(IIF(sa.[Status] IN ('Full', 'Partial', 'None'), 1, 0)) AS OrdersAnyStock,
SUM(IIF(sa.RequisitionDate < dbo.TicksToTime(ma.DailyOrderCutOffTime, @toDate),
IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END), 0)) AS AvailableBeforeCutOff
FROM MasterAccount ma
INNER JOIN StockAvailability sa ON sa.AccountNumber = ma.TaskAccountId
GROUP BY ma.MasterAccountId, ma.IsPartialStock
),
Totals AS
(
SELECT
o.MasterAccountId,
COUNT(o.MasterAccountId) AS BatchedOrders
FROM Orders o
GROUP BY o.MasterAccountId
)
SELECT a.MasterAccountId,
ISNULL(t.BatchedOrders, 0) BatchedOrders,
ISNULL(t.PendingOrders, 0) PendingOrders,
ISNULL(av.AvailableStock, 0) AvailableOrders,
ISNULL(av.AvailableBeforeCutOff, 0) AvailableCutOff,
ISNULL(av.OrdersAnyStock, 0) AllOrders
FROM MasterAccount a
LEFT OUTER JOIN Available av ON av.MasterAccountId = a.MasterAccountId
LEFT OUTER JOIN Totals t ON t.MasterAccountId = a.MasterAccountId
WHERE a.IsActive = 1
Запрос 2: использование временных таблиц:
DROP TABLE IF EXISTS #Orders
CREATE TABLE #Orders (MasterAccountId int, [Status] int);
INSERT INTO #Orders
SELECT
ma.MasterAccountId,
dbo.fn_GetBatchPickingStatus(ma.BatchPickingOnHold,
iif(r.GroupNumber > 0, 1, 0),
iif(r.FinalisedDate is null, 1, 0)) [Status]
FROM MasterAccount ma (nolock)
INNER JOIN wh3.dbo.tblAccounts a (nolock) on a.AccountNumber = dbo.fn_RemoveUnitPrefix(ma.TaskAccountId) and a.IsActive = 1
LEFT OUTER JOIN wh3.dbo.tblRequisitions r (nolock) on r.AccountNumber = a.AccountNumber
WHERE cast(r.BatchDateTime as date) between @fromDate and @toDate
AND r.BatchNumber > 0
AND ma.IsActive = 1
DROP TABLE IF EXISTS #StockAvailability
Create Table #StockAvailability (AccountNumber int, RequisitionNumber int, RequisitionDate datetime, Lines int, HasStock int, NoStock int);
Insert Into #StockAvailability
SELECT
r.AccountNumber,
r.RequisitionNumber,
r.RequisitionDate,
COUNT(rl.ProductNumber) Lines,
SUM(IIF(ISNULL(psoh.AvailableStock, 0) >= ISNULL(rl.Quantity, 0), 1, 0)) AS HasStock,
SUM(IIF(ISNULL(psoh.AvailableStock, 0) < ISNULL(rl.Quantity, 0), 1, 0)) AS NoStock
FROM WH3.dbo.tblrequisitions r (nolock)
INNER JOIN WH3.dbo.tblRequisitionLines rl (nolock) ON rl.RequisitionNumber = r.RequisitionNumber
LEFT JOIN ProductStockOnHandSummary psoh (nolock) ON psoh.ProductNumber = rl.ProductNumber -- Joined with View
WHERE r.BatchNumber = 0
AND r.FinalisedDate is null
AND r.RequisitionStatus = 1
AND r.TransactionTypeNumber = 301
GROUP BY r.AccountNumber, r.RequisitionNumber, r.RequisitionDate
DROP TABLE IF EXISTS #StockAvailability2
Create Table #StockAvailability2 (AccountNumber int, RequisitionNumber int, RequisitionDate datetime, Lines int, HasStock int, NoStock int, [Status] nvarchar(7));
Insert Into #StockAvailability2
SELECT sa.AccountNumber,
sa.RequisitionNumber,
sa.RequisitionDate,
sa.Lines,
sa.HasStock,
sa.NoStock,
CASE WHEN sa.Lines = 0 THEN 'Empty'
WHEN sa.HasStock = 0 THEN 'None'
WHEN (sa.Lines > 0 AND sa.Lines > sa.HasStock) THEN 'Partial'
WHEN (sa.Lines > 0 AND sa.Lines <= sa.HasStock) THEN 'Full'
END AS [Status]
FROM #StockAvailability sa
DROP TABLE IF EXISTS #Available
Create Table #Available (MasterAccountId int, AvailableStock int, OrdersAnyStock int, AvailableBeforeCutOff int);
INSERT INTO #Available
SELECT ma.MasterAccountId,
SUM(IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END)) AS AvailableStock,
SUM(IIF(sa.[Status] IN ('Full', 'Partial', 'None'), 1, 0)) AS OrdersAnyStock,
SUM(IIF(sa.RequisitionDate < dbo.TicksToTime(ma.DailyOrderCutOffTime, @toDate),
IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END), 0)) AS AvailableBeforeCutOff
FROM MasterAccount ma (NOLOCK)
INNER JOIN #StockAvailability2 sa ON sa.AccountNumber = dbo.fn_RemoveUnitPrefix(ma.TaskAccountId)
GROUP BY ma.MasterAccountId, ma.IsPartialStock
;WITH Totals AS
(
SELECT
o.MasterAccountId,
COUNT(o.MasterAccountId) AS BatchedOrders,
SUM(IIF(o.[Status] IN (0,1,2), 1, 0)) PendingOrders
FROM #Orders o (NOLOCK)
GROUP BY o.MasterAccountId
)
SELECT a.MasterAccountId,
ISNULL(t.BatchedOrders, 0) BatchedOrders,
ISNULL(t.PendingOrders, 0) PendingOrders,
ISNULL(av.AvailableStock, 0) AvailableOrders,
ISNULL(av.AvailableBeforeCutOff, 0) AvailableCutOff,
ISNULL(av.OrdersAnyStock, 0) AllOrders
FROM MasterAccount a (NOLOCK)
LEFT OUTER JOIN #Available av (NOLOCK) ON av.MasterAccountId = a.MasterAccountId
LEFT OUTER JOIN Totals t (NOLOCK) ON t.MasterAccountId = a.MasterAccountId
WHERE a.IsActive = 1
Ответы
Ответ 1
Ответ прост.
SQL Server не реализует CTE. Он вставляет их, как вы можете видеть из планов выполнения.
Другая СУБД может реализовать ее по-другому, хорошо известным примером является Postgres, который делает материализацию CTE (он по существу создает временные таблицы для CTE за капотом).
Является ли явная материализация промежуточных результатов в явных временных таблицах быстрее, зависит от запроса.
В сложных запросах накладные расходы на запись и чтение промежуточных данных во временные таблицы могут быть компенсированы более эффективными более простыми планами выполнения, которые оптимизатор может сгенерировать.
С другой стороны, в Postgres CTE есть "оптимизационный забор", и движок не может выталкивать предикаты через границу CTE.
Иногда один путь лучше, иногда другой. Когда сложность запроса растет выше определенного порога, оптимизатор не может анализировать все возможные способы обработки данных, и он должен что-то решить. Например, порядок подключения к таблицам. Количество перестановок растет экспоненциально с количеством таблиц на выбор. Оптимизатор имеет ограниченное время для создания плана, поэтому он может сделать плохой выбор, когда все CTE встроены. Когда вы вручную разбиваете сложный запрос на более мелкие более простые, вам нужно понять, что вы делаете, но оптимизатор имеет больше шансов создать хороший план для каждого простого запроса.
Ответ 2
Существуют разные варианты использования для двух и разные преимущества/недостатки.
Общие выражения таблицы
Общие выражения таблицы следует рассматривать как выражения, а не таблицы. В качестве выражений CTE не нужно создавать экземпляры, поэтому оптимизатор запросов может сворачивать его в остальную часть запроса и оптимизировать комбинацию CTE и остальной части запроса.
Временные таблицы
При использовании временных таблиц результаты запроса сохраняются в реальной таблице реального времени в базе данных temp. Результаты запроса могут быть повторно использованы в нескольких запросах, в отличие от CTE, где CTE, если он используется в нескольких отдельных запросах, должен быть частью плана работы в каждом из этих отдельных запросов.
Кроме того, временная таблица может иметь индекс, ключи и т.д. Добавление их в временную таблицу может помочь в оптимизации некоторых запросов и недоступно в CTE, хотя CTE может использовать индексы и ключи в таблицах, лежащих в основе CTE.
Если базовые таблицы в CTE не поддерживают тип оптимизации, который вам нужен, может быть лучше таблица temp.
Ответ 3
Существует несколько причин, по которым Temp table работает лучше, чем CTE и наоборот, в зависимости от конкретного запроса и требования.
ИМО в вашем случае оба запроса не оптимизируются.
Поскольку CTE оценивается каждый раз, когда на него ссылаются. поэтому в вашем случае
SELECT a.MasterAccountId,
ISNULL(t.BatchedOrders, 0) BatchedOrders,
ISNULL(t.PendingOrders, 0) PendingOrders,
ISNULL(av.AvailableStock, 0) AvailableOrders,
ISNULL(av.AvailableBeforeCutOff, 0) AvailableCutOff,
ISNULL(av.OrdersAnyStock, 0) AllOrders
FROM MasterAccount a
LEFT OUTER JOIN Available av ON av.MasterAccountId = a.MasterAccountId
LEFT OUTER JOIN Totals t ON t.MasterAccountId = a.MasterAccountId
WHERE a.IsActive = 1
Этот запрос показывает оценку High Cardinality Таблица MasterAccount оценивается несколько раз. По этой причине она медленная.
В случае таблицы Temp,
SELECT a.MasterAccountId,
ISNULL(t.BatchedOrders, 0) BatchedOrders,
ISNULL(t.PendingOrders, 0) PendingOrders,
ISNULL(av.AvailableStock, 0) AvailableOrders,
ISNULL(av.AvailableBeforeCutOff, 0) AvailableCutOff,
ISNULL(av.OrdersAnyStock, 0) AllOrders
FROM MasterAccount a (NOLOCK)
LEFT OUTER JOIN #Available av (NOLOCK) ON av.MasterAccountId = a.MasterAccountId
LEFT OUTER JOIN Totals t (NOLOCK) ON t.MasterAccountId = a.MasterAccountId
WHERE a.IsActive = 1
Здесь #Available уже оценивается, и результатом является сохранение в таблице temp, так MasterAccount таблица MasterAccount объединяется с Less resultset, поэтому оценка Cardinality меньше. аналогично таблице #Orders.
Оба запроса CTE и Temp table могут быть оптимизированы в вашем случае, таким образом, производительность улучшилась.
Поэтому #Orders должна быть вашей базой temp-таблицы, и вы не должны использовать MasterAccount снова позже. Вместо этого вы должны использовать #Orders.
INSERT INTO #Available
SELECT ma.MasterAccountId,
SUM(IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END)) AS AvailableStock,
SUM(IIF(sa.[Status] IN ('Full', 'Partial', 'None'), 1, 0)) AS OrdersAnyStock,
SUM(IIF(sa.RequisitionDate < dbo.TicksToTime(ma.DailyOrderCutOffTime, @toDate),
IIF(ma.IsPartialStock = 1, CASE WHEN sa.[Status] IN ('Full', 'Partial') THEN 1 ELSE 0 END,
CASE WHEN sa.[Status] = 'Full' THEN 1 ELSE 0 END), 0)) AS AvailableBeforeCutOff
FROM #Orders ma (NOLOCK)
INNER JOIN #StockAvailability2 sa ON sa.AccountNumber = dbo.fn_RemoveUnitPrefix(ma.TaskAccountId)
GROUP BY ma.MasterAccountId, ma.IsPartialStock
Здесь требуется столбец из таблицы MasterAcount, такой как ma.IsPartialStock и т.д., Если это возможно, должно быть включено в таблицу #order. Надеюсь, моя идея понятна.
Нет необходимости в таблице MasterAccount в последнем запросе
SELECT a.MasterAccountId,
ISNULL(t.BatchedOrders, 0) BatchedOrders,
ISNULL(t.PendingOrders, 0) PendingOrders,
ISNULL(av.AvailableStock, 0) AvailableOrders,
ISNULL(av.AvailableBeforeCutOff, 0) AvailableCutOff,
ISNULL(av.OrdersAnyStock, 0) AllOrders
FROM #Available av
LEFT OUTER JOIN Totals t ON t.MasterAccountId = av.MasterAccountId
--WHERE a.IsActive = 1
Я думаю, что нет необходимости в Nolock hint на временную таблицу.
