隨記

SOV Workflow SQL 優化實戰：將四段 UNION ALL 拆成獨立 Table Function

[!info] 目標將原本由四段 UNION ALL 組成的大查詢，拆成彼此獨立的 inline table-valued function，再由最外層查詢統一組裝。這樣做的目的不是只為了可讀性，而是為了降低大量資料掃描、重複 correlated subquery 與非 SARGable 條件造成的 DTU 壓力。

原始 SQL 的主要問題

原始查詢有四個分支：

SoChgFormLine + SoChgFormHeader + SoChgHoldCommand
NewSoFormLine + NewSoFormHeader + NewSoHoldCommand
PriceControlFormLine + PriceControlFormHeader + PriceControlHoldCommand
CreditControlForm + CreditControlFormDetail + CreditControlFormDetailProduct

它們共通的問題如下：

每列結果都執行 HoldReasonC 與 HoldReasonE 的 correlated subquery。
PriceControl 分支用 CASE WHEN A.OrderNumber IS NOT NULL THEN A.OrderNumber ELSE B.OrderNumber END = @trxReferenceNo，屬於非 SARGable 條件，不利索引使用。
最外層一次處理四個分支，難以單獨觀察各自的執行計畫與成本。
若 command table 很大，重複掃描會直接推高 CPU、logical reads 與 DTU。

重構原則

這次重構遵守以下原則：

每個分支先縮小 Header 範圍，只保留 OrderNumber = @trxReferenceNo 的資料。
再由 Header 去 Join Line，避免先掃整張 Line。
對各自的 command table 先做彙總：
HoldReasonC = STRING_AGG(...)
HoldReasonE = STRING_AGG(...)
最後只做一次 LEFT JOIN，取代 correlated subquery。
各分支拆成 inline TVF，保留優化器 inline 展開能力。

[!note] 這裡選擇的是 inline TVF，而不是 multi-statement TVF。因為 inline TVF 通常會被 optimizer 視為可展開查詢，比較能保留良好的執行計畫。

1. SO Change 分支

CREATE OR ALTER FUNCTION sov.ufn_WorkflowSoChgByOrderNumber
(
    @trxReferenceNo NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
WITH HeaderFiltered AS
(
    SELECT
        B.Tenant,
        B.FormNo,
        B.DocType,
        B.BatchId,
        B.OrderNumber,
        B.OuCode,
        B.OuGroup,
        B.CustCode,
        B.CustName,
        B.Status AS HeaderStatus,
        B.TrackingId,
        B.LastUpdatedDate,
        B.ModelFrom
    FROM sov.SoChgFormHeader B WITH (NOLOCK)
    WHERE B.OrderNumber = @trxReferenceNo
),
LineFiltered AS
(
    SELECT
        A.Tenant,
        A.FormNo,
        A.HoldSourceId,
        A.OrderHoldId,
        A.LineNumber,
        A.Status,
        A.IsNotify,
        A.TrxLineId
    FROM sov.SoChgFormLine A WITH (NOLOCK)
    INNER JOIN HeaderFiltered H
        ON H.Tenant = A.Tenant
       AND H.FormNo = A.FormNo
),
HoldAgg AS
(
    SELECT
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId,
        STRING_AGG(CAST(C.HoldReasonC AS NVARCHAR(300)), ',') AS HoldReasonC,
        STRING_AGG(CAST(C.HoldReasonE AS NVARCHAR(300)), ',') AS HoldReasonE
    FROM sov.SoChgHoldCommand C WITH (NOLOCK)
    INNER JOIN LineFiltered L
        ON L.FormNo = C.FormNo
       AND L.HoldSourceId = C.HoldSourceId
       AND L.OrderHoldId = C.OrderHoldId
    GROUP BY
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId
)
SELECT
    L.Tenant,
    L.FormNo,
    'SO-STD-CHG23' AS FormType,
    ISNULL(H.DocType, 'SO-STD-CHG3') AS DocType,
    ISNULL(H.BatchId, L.HoldSourceId) AS BatchId,
    L.OrderHoldId,
    L.LineNumber,
    CASE
        WHEN L.Status = 'A' THEN 'Approved'
        WHEN L.Status = 'R' THEN 'Reject'
        WHEN L.Status IS NULL THEN NULL
        ELSE L.Status
    END AS Status,
    H.OrderNumber AS TrxReferenceNo,
    H.OuCode,
    H.OuGroup,
    H.CustCode,
    H.CustName,
    H.HeaderStatus,
    'SOV Model' AS ModelName,
    H.TrackingId,
    'B6F32682-B32B-484C-B659-7134C1EAF672' AS FormTypeId,
    H.LastUpdatedDate,
    A.HoldReasonC,
    A.HoldReasonE,
    L.IsNotify,
    H.ModelFrom,
    L.TrxLineId
FROM LineFiltered L
INNER JOIN HeaderFiltered H
    ON H.Tenant = L.Tenant
   AND H.FormNo = L.FormNo
LEFT JOIN HoldAgg A
    ON A.FormNo = L.FormNo
   AND A.HoldSourceId = L.HoldSourceId
   AND A.OrderHoldId = L.OrderHoldId;

這段的改善點

SoChgFormHeader 先被 OrderNumber 篩掉大部分資料。
SoChgHoldCommand 不再對每筆結果重複查詢兩次。
HoldReasonC/HoldReasonE 改成先聚合再 join，減少重複掃描。

2. New SO 分支

CREATE OR ALTER FUNCTION sov.ufn_WorkflowNewSoByOrderNumber
(
    @trxReferenceNo NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
WITH HeaderFiltered AS
(
    SELECT
        B.Tenant,
        B.FormNo,
        B.DocType,
        B.BatchId,
        B.OrderNumber,
        B.OuCode,
        B.OuGroup,
        B.CustCode,
        B.CustName,
        B.Status AS HeaderStatus,
        B.TrackingId,
        B.LastUpdatedDate,
        B.ModelFrom
    FROM sov.NewSoFormHeader B WITH (NOLOCK)
    WHERE B.OrderNumber = @trxReferenceNo
),
LineFiltered AS
(
    SELECT
        A.Tenant,
        A.FormNo,
        A.HoldSourceId,
        A.OrderHoldId,
        A.LineNumber,
        A.Status,
        A.IsNotify,
        A.TrxLineId
    FROM sov.NewSoFormLine A WITH (NOLOCK)
    INNER JOIN HeaderFiltered H
        ON H.Tenant = A.Tenant
       AND H.FormNo = A.FormNo
),
HoldAgg AS
(
    SELECT
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId,
        STRING_AGG(CAST(C.HoldReasonC AS NVARCHAR(300)), ',') AS HoldReasonC,
        STRING_AGG(CAST(C.HoldReasonE AS NVARCHAR(300)), ',') AS HoldReasonE
    FROM sov.NewSoHoldCommand C WITH (NOLOCK)
    INNER JOIN LineFiltered L
        ON L.FormNo = C.FormNo
       AND L.HoldSourceId = C.HoldSourceId
       AND L.OrderHoldId = C.OrderHoldId
    GROUP BY
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId
)
SELECT
    L.Tenant,
    L.FormNo,
    'SO-STD-NEW' AS FormType,
    ISNULL(H.DocType, 'SO-STD2') AS DocType,
    ISNULL(H.BatchId, L.HoldSourceId) AS BatchId,
    L.OrderHoldId,
    L.LineNumber,
    CASE
        WHEN L.Status = 'A' THEN 'Approved'
        WHEN L.Status = 'R' THEN 'Reject'
        WHEN L.Status IS NULL THEN NULL
        ELSE L.Status
    END AS Status,
    H.OrderNumber AS TrxReferenceNo,
    H.OuCode,
    H.OuGroup,
    H.CustCode,
    H.CustName,
    H.HeaderStatus,
    'SOV Model' AS ModelName,
    H.TrackingId,
    'DA835C34-55BE-4D9F-8E11-49B308E50CA9' AS FormTypeId,
    H.LastUpdatedDate,
    A.HoldReasonC,
    A.HoldReasonE,
    L.IsNotify,
    H.ModelFrom,
    L.TrxLineId
FROM LineFiltered L
INNER JOIN HeaderFiltered H
    ON H.Tenant = L.Tenant
   AND H.FormNo = L.FormNo
LEFT JOIN HoldAgg A
    ON A.FormNo = L.FormNo
   AND A.HoldSourceId = L.HoldSourceId
   AND A.OrderHoldId = L.OrderHoldId;

這段的改善點

結構與 SO Change 相同，可直接形成一致化維護模式。
若後續要調整 HoldReason 聚合邏輯，只需看單一 function。

3. Price Control 分支

這段是四個分支中最值得優先處理的一段，因為原始條件：

CASE WHEN A.OrderNumber IS NOT NULL THEN A.OrderNumber ELSE B.OrderNumber END = @trxReferenceNo

這種寫法會降低索引命中機率，因此重構時拆成兩段 UNION ALL。

CREATE OR ALTER FUNCTION sov.ufn_WorkflowPriceControlByOrderNumber
(
    @trxReferenceNo NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
WITH HeaderFiltered AS
(
    SELECT
        B.Tenant,
        B.FormNo,
        B.FormType,
        B.BatchId,
        B.OrderNumber,
        B.OuCode,
        B.OuGroup,
        B.CustCode,
        B.CustName,
        B.Status AS HeaderStatus,
        B.TrackingId,
        B.LastUpdatedDate
    FROM sov.PriceControlFormHeader B WITH (NOLOCK)
    WHERE B.FormType <> 'DO-M-C'
      AND B.OrderNumber = @trxReferenceNo
),
LineFromOwnOrderNo AS
(
    SELECT
        A.Tenant,
        A.FormNo,
        A.HoldSourceId,
        A.OrderHoldId,
        A.LineNumber,
        A.Status,
        A.IsNotify,
        A.TrxLineId,
        A.OrderNumber AS TrxReferenceNo,
        A.LineTrackingId
    FROM sov.PriceControlFormLine A WITH (NOLOCK)
    WHERE A.OrderNumber = @trxReferenceNo
),
LineFromHeaderOrderNo AS
(
    SELECT
        A.Tenant,
        A.FormNo,
        A.HoldSourceId,
        A.OrderHoldId,
        A.LineNumber,
        A.Status,
        A.IsNotify,
        A.TrxLineId,
        H.OrderNumber AS TrxReferenceNo,
        A.LineTrackingId
    FROM sov.PriceControlFormLine A WITH (NOLOCK)
    INNER JOIN HeaderFiltered H
        ON H.Tenant = A.Tenant
       AND H.FormNo = A.FormNo
    WHERE A.OrderNumber IS NULL
),
LineFiltered AS
(
    SELECT * FROM LineFromOwnOrderNo
    UNION ALL
    SELECT * FROM LineFromHeaderOrderNo
),
HoldAgg AS
(
    SELECT
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId,
        STRING_AGG(CAST(C.HoldReasonC AS NVARCHAR(300)), ',') AS HoldReasonC,
        STRING_AGG(CAST(C.HoldReasonE AS NVARCHAR(300)), ',') AS HoldReasonE
    FROM sov.PriceControlHoldCommand C WITH (NOLOCK)
    INNER JOIN LineFiltered L
        ON L.FormNo = C.FormNo
       AND L.HoldSourceId = C.HoldSourceId
       AND L.OrderHoldId = C.OrderHoldId
    GROUP BY
        C.FormNo,
        C.HoldSourceId,
        C.OrderHoldId
)
SELECT DISTINCT
    L.Tenant,
    L.FormNo,
    'SO-M-C_WorstPrice' AS FormType,
    ISNULL(H.FormType, 'SO-STD2') AS DocType,
    ISNULL(H.BatchId, L.HoldSourceId) AS BatchId,
    L.OrderHoldId,
    L.LineNumber,
    CASE
        WHEN L.Status = 'A' THEN 'Approved'
        WHEN L.Status = 'R' THEN 'Reject'
        WHEN L.Status IS NULL THEN NULL
        ELSE L.Status
    END AS Status,
    L.TrxReferenceNo,
    H.OuCode,
    H.OuGroup,
    H.CustCode,
    H.CustName,
    H.HeaderStatus,
    'SOV Model' AS ModelName,
    ISNULL(L.LineTrackingId, H.TrackingId) AS TrackingId,
    '1C2B3DA9-9204-4BD9-BD02-F91D912ADD75' AS FormTypeId,
    H.LastUpdatedDate,
    A.HoldReasonC,
    A.HoldReasonE,
    L.IsNotify,
    'PriceControl' AS ModelFrom,
    L.TrxLineId
FROM LineFiltered L
INNER JOIN HeaderFiltered H WITH (NOLOCK)
    ON H.Tenant = L.Tenant
   AND H.FormNo = L.FormNo
LEFT JOIN HoldAgg A
    ON A.FormNo = L.FormNo
   AND A.HoldSourceId = L.HoldSourceId
   AND A.OrderHoldId = L.OrderHoldId;

這段的改善點

移除 CASE 包住搜尋欄位的寫法。
先分別處理 A.OrderNumber 有值與為 NULL 的情境。
PriceControlHoldCommand 仍然只聚合一次。

[!warning] 這段還保留 SELECT DISTINCT。若後續 execution plan 顯示 DISTINCT 成本高，應再往前追查是否有 Join 條件造成重複列，而不是長期依賴 DISTINCT 消重。

4. Credit Control 分支

CREATE OR ALTER FUNCTION sov.ufn_WorkflowCreditControlByOrderNumber
(
    @trxReferenceNo NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
SELECT
    A.Tenant,
    A.FormNo,
    'CreditControl' AS FormType,
    'CreditControl' AS DocType,
    B.SoMoNumber AS BatchId,
    B.SoMoNumber AS OrderHoldId,
    C.LineNumber,
    CASE
        WHEN A.FormStatus = 'C' THEN 'Approve'
        WHEN A.FormStatus = 'R' THEN 'Reject'
        ELSE 'Approving'
    END AS Status,
    A.SoDoNumber AS TrxReferenceNo,
    A.OuCode,
    '' AS OuGroup,
    A.CustomerCode AS CustCode,
    A.CustomerNameCn AS CustName,
    CASE
        WHEN A.FormStatus = 'C' THEN 'Approve'
        WHEN A.FormStatus = 'R' THEN 'Reject'
        ELSE 'Approving'
    END AS HeaderStatus,
    'CreditControl' AS ModelName,
    CONVERT(NVARCHAR(50), B.TrackingId) AS TrackingId,
    CASE
        WHEN A.FormType = 'PRESHIP' THEN 'F47AC10B-58CC-4372-A567-0E02B2C3D479'
        ELSE '227FF6F2-3CC6-BB59-A512-532B13476E21'
    END AS FormTypeId,
    A.LastUpdateDate AS LastUpdatedDate,
    B.ApprovalInfo AS HoldReasonC,
    B.ApprovalInfo AS HoldReasonE,
    'N' AS IsNotify,
    'Credit Model' AS ModelFrom,
    C.LineNumber AS TrxLineId
FROM credit.CreditControlForm A WITH (NOLOCK)
INNER JOIN credit.CreditControlFormDetail B WITH (NOLOCK)
    ON A.FormNo = B.FormNo
INNER JOIN credit.CreditControlFormDetailProduct C WITH (NOLOCK)
    ON A.FormNo = C.FormNo
WHERE A.SoDoNumber = @trxReferenceNo;

這段的改善點

相較前三段，這段沒有 command table correlated subquery，因此改動較小。
仍建議確認 CreditControlFormDetail 與 CreditControlFormDetailProduct 是否真的只靠 FormNo 關聯。

[!danger] 如果 Detail 與 DetailProduct 實際上是多對多展開，但目前只用 FormNo Join，可能會造成結果倍增，這會直接放大 I/O 與 DTU。這一段建議優先做資料模型確認。

最終組裝 SQL

四個 function 完成後，最外層只需要做統一 UNION ALL：

SELECT
    Q.Tenant,
    Q.FormNo,
    Q.FormType,
    Q.DocType,
    Q.BatchId,
    Q.OrderHoldId,
    Q.LineNumber,
    Q.Status,
    Q.TrxReferenceNo,
    Q.OuCode,
    Q.OuGroup,
    Q.CustCode,
    Q.CustName,
    Q.HeaderStatus,
    Q.ModelName,
    Q.TrackingId,
    Q.FormTypeId,
    Q.LastUpdatedDate,
    Q.HoldReasonC,
    Q.HoldReasonE,
    Q.IsNotify,
    Q.ModelFrom,
    Q.TrxLineId,
    ROW_NUMBER() OVER (ORDER BY Q.FormNo) AS Num
FROM
(
    SELECT * FROM sov.ufn_WorkflowSoChgByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowNewSoByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowPriceControlByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowCreditControlByOrderNumber(@trxReferenceNo)
) Q;

建議索引

至少補這幾類索引：

CREATE INDEX IX_SoChgFormHeader_OrderNumber
ON sov.SoChgFormHeader(OrderNumber, Tenant, FormNo);

CREATE INDEX IX_NewSoFormHeader_OrderNumber
ON sov.NewSoFormHeader(OrderNumber, Tenant, FormNo);

CREATE INDEX IX_PriceControlFormHeader_OrderNumber
ON sov.PriceControlFormHeader(OrderNumber, Tenant, FormNo, FormType);

CREATE INDEX IX_CreditControlForm_SoDoNumber
ON credit.CreditControlForm(SoDoNumber, FormNo);

Line 類表

CREATE INDEX IX_SoChgFormLine_Tenant_FormNo
ON sov.SoChgFormLine(Tenant, FormNo)
INCLUDE (HoldSourceId, OrderHoldId, LineNumber, Status, IsNotify, TrxLineId);

CREATE INDEX IX_NewSoFormLine_Tenant_FormNo
ON sov.NewSoFormLine(Tenant, FormNo)
INCLUDE (HoldSourceId, OrderHoldId, LineNumber, Status, IsNotify, TrxLineId);

CREATE INDEX IX_PriceControlFormLine_Tenant_FormNo
ON sov.PriceControlFormLine(Tenant, FormNo)
INCLUDE (HoldSourceId, OrderHoldId, LineNumber, Status, IsNotify, TrxLineId, OrderNumber, LineTrackingId);

Command 類表

CREATE INDEX IX_SoChgHoldCommand_Key
ON sov.SoChgHoldCommand(FormNo, HoldSourceId, OrderHoldId)
INCLUDE (HoldReasonC, HoldReasonE);

CREATE INDEX IX_NewSoHoldCommand_Key
ON sov.NewSoHoldCommand(FormNo, HoldSourceId, OrderHoldId)
INCLUDE (HoldReasonC, HoldReasonE);

CREATE INDEX IX_PriceControlHoldCommand_Key
ON sov.PriceControlHoldCommand(FormNo, HoldSourceId, OrderHoldId)
INCLUDE (HoldReasonC, HoldReasonE);

預期收益

重構完成後，理論上會有以下收益：

command table 不再對每一列結果重複掃描。
各分支的 I/O 能先被 @trxReferenceNo 收斂。
執行計畫更容易獨立觀察與調校。
維護時可以單獨修改某個 function，而不是在超長 SQL 裡找片段。

仍需注意的地方

NOLOCK 只會改變讀取行為，不會直接降低 CPU 或 logical reads；它不是效能優化手段。
ROW_NUMBER() OVER (ORDER BY FormNo) 仍需要排序，資料量大時要關注 sort cost。
PriceControl 的 DISTINCT 與 CreditControl 的 join 仍是後續可能的優化點。
若參數分布極端不平均，需另外觀察是否有 parameter sniffing 問題。

結論

這次重構的重點不是把 SQL 切碎，而是把原本「每列重複查詢」的模式，改成「先縮小範圍、先聚合、最後 join」。四個分支拆成獨立 inline TVF 後，能同時提升可維護性與可調校性，也比較容易逐段驗證哪一段才是真正的 DTU 熱點。

ALTER FUNCTION [sov].[QuerysovFormData]
(   
    -- Add the parameters for the function here
    @trxReferenceNo as nvarchar(50)
)
RETURNS  @results table (
    Tenant nvarchar(6),
    FormNo nvarchar(50),
    FormType nvarchar(50),
    DocType nvarchar(50),
    BatchId nvarchar(50),
    OrderHoldId nvarchar(100),
    LineNumber nvarchar(30),
    [Status] nvarchar(10),
    TrxReferenceNo nvarchar(100),
    OuCode nvarchar(10),
    OuGroup nvarchar(20),
    CustCode nvarchar(20),
    CustName nvarchar(600),
    HeaderStatus nvarchar(10),
    ModelName nvarchar(15),
    TrackingId nvarchar(50),
    FormTypeId nvarchar(50),
    LastUpdatedDate datetimeoffset(7),
    HoldReasonC nvarchar(1000),
    HoldReasonE nvarchar(1000),
    IsNotify nvarchar(2),
    ModelFrom nvarchar(50),
    TrxLineId nvarchar(20),
    num bigInt

  ) as 
begin
    insert @results
        (Tenant,FormNo,FormType,DocType,BatchId,OrderHoldId,LineNumber,[Status],TrxReferenceNo,OuCode,OuGroup,CustCode,CustName,
        HeaderStatus,ModelName,TrackingId,FormTypeId,LastUpdatedDate,HoldReasonC,HoldReasonE,IsNotify,ModelFrom,TrxLineId,num)
  SELECT
    Q.Tenant,
    Q.FormNo,
    Q.FormType,
    Q.DocType,
    Q.BatchId,
    Q.OrderHoldId,
    Q.LineNumber,
    Q.Status,
    Q.TrxReferenceNo,
    Q.OuCode,
    Q.OuGroup,
    Q.CustCode,
    Q.CustName,
    Q.HeaderStatus,
    Q.ModelName,
    Q.TrackingId,
    Q.FormTypeId,
    Q.LastUpdatedDate,
    LEFT(Q.HoldReasonC,1000),
    LEFT(Q.HoldReasonE,1000),
    Q.IsNotify,
    Q.ModelFrom,
    Q.TrxLineId,
    ROW_NUMBER() OVER (ORDER BY Q.FormNo) AS Num
FROM
(
    SELECT * FROM sov.ufn_WorkflowSoChgByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowNewSoByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowPriceControlByOrderNumber(@trxReferenceNo)
    UNION ALL
    SELECT * FROM sov.ufn_WorkflowCreditControlByOrderNumber(@trxReferenceNo)
) Q;

    return
end  


GO

SOV Workflow SQL 優化實戰：將四段 UNION ALL 拆成獨立 Table Function

原始 SQL 的主要問題

重構原則

1. SO Change 分支

這段的改善點

2. New SO 分支

這段的改善點

3. Price Control 分支

這段的改善點

4. Credit Control 分支

這段的改善點

最終組裝 SQL

建議索引

Header 類表

Line 類表

Command 類表

預期收益

仍需注意的地方

結論