with CTE(共通テーブル式) が SQL Server の一時テーブルと比較してクエリを遅くする理由




with cte as sql (3)

答えは簡単です。

SQL ServerはCTEを具体化しません。 実行計画からわかるように、それらはインライン化されます。

他のDBMSはそれを異なって実装するかもしれません、よく知られている例はCTEを具体化するPostgresです(それは本質的にフードの後ろでCTEのための一時的なテーブルを作成します)。

中間の明示的な実体化が明示的な一時テーブルをもたらすかどうかは、クエリによって異なります。

複雑なクエリでは、中間データを一時テーブルに書き込んだり読み込んだりすることによるオーバーヘッドは、オプティマイザが生成できるより効率的で単純な実行計画によって相殺される可能性があります。

一方、PostgresではCTEは「最適化フェンス」であり、エンジンはCTEの境界を越えて述語をプッシュすることはできません。

時にはある方法が良い、時には別の方法があります。 クエリの複雑さが一定のしきい値を超えると、最適化担当者はデータを処理するために考えられるすべての方法を分析できず、何かを決定しなければなりません。 たとえば、テーブルを結合する順序です。 順列の数は、選択するテーブルの数とともに指数関数的に増加します。 オプティマイザーは計画を作成する時間が限られているため、すべてのCTEがインライン化されている場合は選択が不適切になることがあります。 複雑なクエリを手動で小さい単純なクエリに分割する場合は、自分が何をしているのかを理解する必要がありますが、optimiserは単純なクエリごとに適切な計画を生成する機会が多くなります。

SQL Server一時テーブルを使用した同じクエリよりも、複雑なCTECommon Table Expressions )が10倍遅くなるケースがいくつかありSQL Server

ここでの私の質問は、 SQL ServerCTEクエリをどのように処理するかということです。各クエリの結果を格納してから次のクエリを実行するのではなく、すべての分離クエリを結合しようとします。 そのため、一時テーブルを使用すると高速になる理由が考えられます。

例えば:

クエリ1Common 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

特定のクエリと要件に応じて、 Temp tableCTEよりも優れたパフォーマンスを示し、またその逆の場合もあります。

あなたのケースではIMOは両方のクエリが最適化されていません。

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テーブルは複数回評価されます。このため、遅いです。

温度テーブルの場合

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テーブルはLess MasterAccountと結合されているため、Cardinality Estimateは小さくなります。 #Ordersテーブルも同様です。

あなたのケースではCTEとTempテーブルクエリの両方が最適化され、パフォーマンスが向上します。

そのため、 #Ordersをベースの一時テーブルにして、後で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

ここでma.IsPartialStockなどのMasterAcountテーブルの列が必要な場合は#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は必要ないと思います。


両者には異なるユースケースがあり、それぞれ長所と短所が異なります。

一般的なテーブル式

共通表式は、 ではなくとして表示する必要があります。 式として、CTEをインスタンス化する必要はないため、クエリオプティマイザはそれを残りのクエリに折りたたみ、CTEと残りのクエリの組み合わせを最適化できます。

一時テーブル

一時テーブルでは、クエリの結果は一時データベースの実際のライブテーブルに格納されます。 CTEが複数の個別のクエリで使用される場合、それらの個別の各クエリで作業計画の一部になる必要があるCTEとは異なり、クエリの結果は複数のクエリで再利用できます。

また、テンポラリテーブルにはインデックス、キーなどを含めることができます。テンポラリテーブルにこれらを追加すると、一部のクエリを最適化するのに非常に役立ちます。また、CTEでは使用できません。 CTE

CTEの基礎となるテーブルが必要な最適化の種類をサポートしていない場合は、一時テーブルの方が良い場合があります。





sql-server-2012