为什么在某些情况下CTE（公用表表达式）与SQL Server中的临时表相比会使查询变慢

Question

在几种情况下，我的complex CTE（Common Table Expressions）比使用中的临时表进行的查询慢10倍SQLServer。

我的问题是关于查询的SQLServer处理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

Answer 1

答案很简单。

SQL Server没有实现CTE。从执行计划中可以看出，它可以内联它们。

其他DBMS可能会以不同的方式实现它，一个著名的例子是Postgres，它确实实现了CTE（它实际上为幕后的CTE创建了临时表）。

中介的显式实现具体化在显式临时表中是否更快，取决于查询。

在复杂的查询中，通过优化程序能够生成的更有效，更简单的执行计划，可以抵消将中间数据写入和读取临时表的开销。

另一方面，在Postgres中，CTE是“优化围栏”，引擎无法将谓词推入CTE边界。

有时一种方法更好，有时另一种方法。一旦查询复杂度超过特定阈值，优化器将无法分析所有可能的数据处理方式，因此必须依靠某种方式。例如，联接表的顺序。排列的数量与要选择的表的数量成指数增长。Optimizer生成计划的时间有限，因此当内联所有CTE时，它可能是一个糟糕的选择。当您手动将复杂查询分解为较小的简单查询时，您需要了解您的操作，但优化程序有更好的机会为每个简单查询生成良好的计划。