同事的一條 10 秒慢查詢,通過 CTE 重構降至 5 秒。本文實戰拆解這個優化案例,展示 CTE 如何消除重複子查詢、簡化邏輯、提升可讀性,並延伸介紹遞歸 CTE 在層次結構處理中的威力。
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;
Base_Staff 和 Rpt_RegistFormWITH StaffRegist AS (
-- 1. 一次性完成核心關聯,複用多次
SELECT
S.Name,
S.AccountantCode,
R.BusinessBackupCustomerId
-- R.Id, 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
),
ReportAgg AS (
-- 2. 聚合報告數
SELECT Name, AccountantCode, COUNT(BusinessBackupCustomerId) AS ReportCount
FROM StaffRegist
GROUP BY Name, AccountantCode
)
SELECT
T0.*,
-- 3. 關聯業務表計算約定書數,利用 CTE 結果集
(SELECT COUNT(*) FROM (
SELECT DISTINCT BusinessBackupId
FROM Biz_BusinessBackupCustomer B
INNER JOIN StaffRegist SR ON B.Id = SR.BusinessBackupCustomerId
WHERE T0.AccountantCode = SR.AccountantCode
) T1) AS AgreementCount
FROM ReportAgg T0;
| 優化點 | 原始寫法 | CTE 重構 | 收益 |
|---|---|---|---|
| 核心關聯 | 執行 2 次(子查詢 + LEFT JOIN) | CTE 執行 1 次,複用 2 次 | 掃描/連接成本減半 |
| 聚合邏輯 | 分散在 SELECT 列表 | 專用 CTE 聚合 | 計劃更簡單、可並行 |
| 相關子查詢 | 逐行執行 | 轉為 JOIN + 聚合 | 批次處理,利用索引 |
| 可讀性 | 嵌套 4 層,難維護 | 分步 CTE,邏輯清晰 | 維護成本大幅降低 |
代碼中 COUNT(BusinessBackupCustomerId) 而非 COUNT(*):
COUNT(column) 忽略 NULL 值BusinessBackupCustomerId 為 NULL-- 需求:查詢某員工的所有下屬(含間接),按層級排序
WITH Subordinates AS (
SELECT EmployeeID, ManagerID, LoginID, 1 AS Level
FROM HumanResources.Employee
WHERE ManagerID = @ManagerId -- 定點:直屬下屬
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.LoginID, s.Level + 1
FROM HumanResources.Employee e
JOIN Subordinates s ON e.ManagerID = s.EmployeeID
WHERE s.Level < 10 -- 安全閥
)
SELECT * FROM Subordinates ORDER BY Level, LoginID
OPTION (MAXRECURSION 100);
實戰感悟:CTE 是「邏輯重構工具」,不是「性能銀彈」。真正的性能提升來自於消除冗餘計算、引導優化器生成更優計劃、將相關子查詢轉為集合操作。寫出清晰 CTE 的過程,本質上是對查詢邏輯的深度梳理——這才是最大的收穫。