隨記

SQL 效能優化分析

問題摘要

這段查詢的主要負擔不在單一 JOIN，而是多個高成本操作疊加：

  • 每列重複執行相關子查詢，特別是 FOR XML PATH 的 HoldReasonC 與 HoldReasonE。
  • 第三段使用 CASE WHEN ... 形成非 SARGable 條件，索引利用率差。
  • DISTINCT 可能掩蓋了 join 膨脹，但本身會增加排序或 hash 成本。
  • CreditControl 區塊可能存在乘積放大風險，因為 detail 與 product 兩張表只用 FormNo 串接。
  • 外層 SELECT * 會增加 I/O、記憶體與網路傳輸成本。

高優先級優化建議

1. 移除 correlated subquery

目前每一列都會對 command table 再掃一次，成本很高。應改成先對 command table 依 (FormNo, HoldSourceId, OrderHoldId) 聚合，再 LEFT JOIN 回主查詢。

如果 SQL Server 版本支援，可優先用 STRING_AGG；若版本較舊，再用 XML 聚合，但也要先彙總後再 join。

2. 改寫非 SARGable 條件

這段條件：

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

建議改成：

WHERE (A.OrderNumber = @trxReferenceNo) OR (A.OrderNumber IS NULL AND B.OrderNumber = @trxReferenceNo)

更好的方式是直接拆成兩段 UNION ALL，讓每段都能更容易走索引。

3. 檢查 DISTINCT 是否必要

如果是因為 join 寫法導致重複，應修正關聯鍵，而不是用 DISTINCT 事後去重。

4. 修正 CreditControl 的 join 粒度

CreditControlFormDetail 與 CreditControlFormDetailProduct 目前都只依 FormNo 連接，這很容易把資料量放大成 N x M。

應確認是否還有其他明確關聯鍵，例如 detail id、line number 或 product key，避免無意間拉爆結果集。

5. 不要用 SELECT *

外層應只保留真正需要的欄位，減少資料搬運成本。

索引建議

Header / Line 表

  • SoChgFormHeader(OrderNumber, Tenant, FormNo)
  • SoChgFormLine(Tenant, FormNo)
  • NewSoFormHeader(OrderNumber, Tenant, FormNo)
  • NewSoFormLine(Tenant, FormNo)
  • PriceControlFormHeader(OrderNumber, Tenant, FormNo)
  • PriceControlFormLine(Tenant, FormNo)

Command 表

  • SoChgHoldCommand(FormNo, HoldSourceId, OrderHoldId)
  • NewSoHoldCommand(FormNo, HoldSourceId, OrderHoldId)
  • PriceControlHoldCommand(FormNo, HoldSourceId, OrderHoldId)

並視查詢欄位加入 INCLUDE(HoldReasonC, HoldReasonE)。

CreditControl 表

  • CreditControlForm(SoDoNumber, FormNo)
  • CreditControlFormDetail(FormNo, ...)
  • CreditControlFormDetailProduct(FormNo, ...)

實際索引欄位仍要依真正的關聯鍵補齊。

進一步優化

  • ROW_NUMBER() OVER (ORDER BY FormNo) 會觸發排序；如果只是畫面顯示序號，可在應用層處理。
  • NOLOCK 不會根治效能問題，只是降低鎖等待的表象，還會帶來髒讀與重複讀風險。
  • 如果 @trxReferenceNo 的資料分布很偏斜，可評估 OPTION (RECOMPILE)，但要先測試實際計畫。

下一步建議

  1. 先看實際 execution plan，確認是否卡在 lookup、scan、sort 或 hash join。
  2. 先重寫 HoldReason 聚合邏輯，這通常是最直接的降 DTU 手段。
  3. 再檢查 CreditControl 的 join 是否造成結果膨脹。