향상 - sql 속도 개선




경우에 따라 CTE(Common Table Expressions)가 SQL Server의 임시 테이블과 비교하여 쿼리 속도를 저하시키는 이유 (2)

복잡한 CTE ( Common Table Expressions )가 SQL Server 의 임시 테이블을 사용하는 동일한 쿼리보다 10 배 더 느린 경우가 몇 가지 있습니다.

여기 내 질문은 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

대답은 간단합니다.

SQL Server는 CTE를 구체화하지 않습니다. 당신이 실행 계획에서 볼 수 있듯이, 그것들을 인라인합니다.

다른 DBMS는이를 다르게 구현할 수 있습니다. 잘 알려진 예로 Postgres가 있습니다. Postgres는 CTE를 구체화합니다 (본질적으로 CTE의 임시 테이블을 생성합니다).

명시 적 임시 테이블에서 중개 결과의 명시적인 materialization이 더 빠른지 여부는 쿼리에 따라 다릅니다.

복잡한 쿼리에서 중개자 데이터를 임시 테이블로 작성하고 읽는 오버 헤드는 최적화 프로그램이 생성 할 수있는보다 효율적인 간단한 실행 계획에 의해 상쇄 될 수 있습니다.

반면 Postgres CTE는 "최적화 울타리"이며 엔진은 CTE 경계를 넘어서 술어를 푸시 할 수 없습니다.

때로는 편도가 더 좋고 때로는 또 다른 편입니다. 쿼리 복잡성이 특정 임계 값을 초과하면 옵티마이 저는 데이터를 처리 할 수있는 모든 가능한 방법을 분석 할 수 없으며 무언가를 해결해야합니다. 예를 들어, 테이블을 조인 할 순서. 선택할 수있는 테이블 수에 따라 순열 수가 기하 급수적으로 증가합니다. Optimizer는 계획을 생성하는 데 제한된 시간을 가지므로 모든 CTE가 인라인 될 때 ​​잘못된 선택을 할 수 있습니다. 복잡한 쿼리를 수동으로 더 작은 간단한 쿼리로 나눌 때 수행중인 작업을 이해해야하지만 옵티마이 저는 간단한 쿼리 각각에 대해 좋은 계획을 생성 할 수 있습니다.


두 가지 경우의 사용 사례가 다르며 장점 / 단점이 다릅니다.

공통 테이블 식

공통 표 표현식은 표가 아닌 표현식 으로 표시 되어야합니다. 표현식으로 CTE를 인스턴스화 할 필요가 없으므로 쿼리 최적화 프로그램이 CTE를 나머지 쿼리로 폴드하고 CTE와 나머지 쿼리의 조합을 최적화 할 수 있습니다.

임시 테이블

임시 테이블의 경우 쿼리 결과는 임시 데이터베이스의 실제 라이브 테이블에 저장됩니다. 그런 다음 CTE (여러 별도의 쿼리에서 사용되는 경우)가 각각의 개별 쿼리에서 작업 계획의 일부가되어야하는 CTE와 달리 쿼리 결과를 여러 쿼리에서 다시 사용할 수 있습니다.

또한 임시 테이블에는 인덱스, 키 등이있을 수 있습니다. 이러한 테이블을 임시 테이블에 추가하면 CTE에서 일부 쿼리를 최적화하는 데 큰 도움이 될 수 있으며 CTE는 기본 테이블의 인덱스와 키를 활용할 수 있지만 CTE.

CTE에 대한 기본 테이블이 필요한 최적화 유형을 지원하지 않으면 임시 테이블이 더 좋을 수 있습니다.





sql-server-2012