善用 SQL Server CTE 解決複雜查詢效能問題：實戰案例分析

問題背景

同事編寫查詢：從 Base_Staff (人員) 關聯 Rpt_RegistForm (報備單) 統計約定書與報告數量。原查詢執行 ~10 秒，且重複掃描相同關聯結果集兩次 (一次統計約定書、一次統計報告)。

原始查詢 (簡化)

SELECT S.Name, S.AccountantCode,
  (SELECT COUNT(*) FROM (
     SELECT DISTINCT BusinessBackupId 
     FROM Biz_BusinessBackupCustomer 
     WHERE Id IN (
       SELECT BusinessBackupCustomerId 
       FROM Rpt_RegistForm 
       WHERE (SignatureCPA1Id=S.Id OR SignatureCPA2Id=S.Id) AND DocStatus=30
     )
   ) T) AS BNum,
  CASE WHEN R.Id IS NULL THEN 0 ELSE 1 END AS Num,
  R.ReportBackupDate
FROM Base_Staff S
LEFT JOIN Rpt_RegistForm R 
  ON (R.SignatureCPA1Id=S.Id OR R.SignatureCPA2Id=S.Id) AND R.DocStatus=30
WHERE S.UserType=3;

CTE 重構：提取共用關聯結果集

WITH StaffRegist AS (
  SELECT 
    S.Name, S.AccountantCode, S.Id AS StaffId,
    R.BusinessBackupCustomerId, R.Id AS RegistId,
    R.SignatureCPA1Id, R.SignatureCPA2Id
  FROM Base_Staff S
  LEFT JOIN Rpt_RegistForm R
    ON (R.SignatureCPA1Id = S.Id OR R.SignatureCPA2Id = S.Id)
    AND R.DocStatus = 30
  WHERE S.UserType = 3
)
SELECT 
  T0.Name, T0.AccountantCode,
  (SELECT COUNT(*) FROM (
     SELECT DISTINCT BusinessBackupId 
     FROM Biz_BusinessBackupCustomer B
     JOIN StaffRegist C ON B.Id = C.BusinessBackupCustomerId
     WHERE T0.AccountantCode = C.AccountantCode
   ) T1) AS 約定書數,
  T0.報告數
FROM (
  SELECT Name, AccountantCode, COUNT(BusinessBackupCustomerId) AS 報告數
  FROM StaffRegist
  GROUP BY Name, AccountantCode
) T0;

效能提升分析

關鍵技術點

1. CTE 非物化：SQL Server 將 CTE 展開為單一查詢樹，不建立暫存表，但優化器能識別共用子運算子，避免重複存取。
2. COUNT(欄位) 忽略 NULL：COUNT(BusinessBackupCustomerId) 自動略過 NULL 列，符合「無報備單則計 0」業務邏輯，無需 CASE。
3. 可讀性：將「人員-報備單」關聯邏輯集中定義，主查詢專注聚合與業務計算。

遞歸 CTE 補充

CTE 亦支援遞歸 (參見 CTE 遞歸指南)，適用於組織架構、BOM、分類樹等層次結構查詢。

最佳實務建議

• 同一查詢中多次引用相同衍生結果集時，優先考慮 CTE。
• CTE 定義中只選取必要欄位，減少資料流寬度。
• 避免在 CTE 內使用非確定性函數 (NEWID(), RAND()) 導致多次評估結果不一致。
• 極大結果集 (百萬列+) 且多次引用時，評估 暫存表 + 索引 是否優於 CTE (CTE 每次引用重新計算)。

結語

CTE 不僅提升可讀性，更能協助優化器識別共用子運算，消除重複掃描。本案例從 10 秒降至 5 秒，證明「邏輯去重」帶來的「物理去重」效果。開發複雜報表查詢時，養成「先定義 CTE 提取核心資料集，再疊加業務聚合」的分層思維。