本文介绍了与 SQL Server 中的临时表相比,为什么 CTE(通用表表达式)在某些情况下会降低查询速度的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有几种情况,我的复杂 CTE(Common Table Expressions)比使用 SQL Server 中的临时表的相同查询慢十倍代码>.

I have several cases where my complex CTE (Common Table Expressions) are ten times slower than the same queries using the temporary tables in SQL Server.

我的问题是关于 SQL Server 如何处理 CTE 查询,看起来它试图连接所有分离的查询而不是存储每个查询的结果一个,然后尝试运行以下的.所以这可能是使用临时表时速度如此之快的原因.

My question here is in regards to how SQL Server process the CTE queries, it looks like it tries to join all the separated queries instead of storing the results of each one and then trying to run the following ones. So that might be the reason why it is so faster when using temporary tables.

例如:

查询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

推荐答案

答案很简单.

SQL Server 不会实现 CTE.正如您从执行计划中看到的那样,它会将它们内联.

SQL Server doesn't materialise CTEs. It inlines them, as you can see from the execution plans.

其他 DBMS 可能以不同的方式实现它,一个众所周知的例子是 Postgres,它确实实现了 CTE(它本质上是在幕后为 CTE 创建临时表).

Other DBMS may implement it differently, a well-known example is Postgres, which does materialise CTEs (it essentially creates temporary tables for CTEs behind the hood).

在显式临时表中显式实现中间结果是否更快,取决于查询.

Whether explicit materialisation of intermediary results in explicit temporary tables is faster, depends on the query.

在复杂查询中,将中间数据写入和读取到临时表的开销可以通过优化器能够生成的更高效的更简单的执行计划来抵消.

In complex queries the overhead of writing and reading intermediary data into temporary tables can be offset by more efficient simpler execution plans that optimiser is able to generate.

另一方面,在 Postgres CTE 是一个优化栅栏",引擎不能将谓词推送到 CTE 边界.

On the other hand, in Postgres CTE is an "optimisation fence" and engine can't push predicates across CTE boundary.

有时一种方式更好,有时另一种方式.一旦查询复杂度增长超过某个阈值,优化器就无法分析处理数据的所有可能方式,它必须解决某些问题.例如,加入表的顺序.排列的数量随着可供选择的表的数量呈指数增长.优化器生成计划的时间有限,因此当所有 CTE 都内联时,它可能会做出糟糕的选择.当您手动将复杂的查询分解为更小的更简单的查询时,您需要了解您在做什么,但优化器有更好的机会为每个简单查询生成一个好的计划.

Sometimes one way is better, sometimes another. Once the query complexity grows beyond certain threshold an optimiser can't analyse all possible ways to process the data and it has to settle on something. For example, the order in which to join the tables. The number of permutations grows exponentially with the number of tables to choose from. Optimiser has limited time to generate a plan, so it may make a poor choice when all CTEs are inlined. When you manually break complex query into smaller simpler ones you need to understand what you are doing, but optimiser has a better chance to generate a good plan for each simple query.

这篇关于与 SQL Server 中的临时表相比,为什么 CTE(通用表表达式)在某些情况下会降低查询速度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-29 08:56