如何优化SQL Server中计算员工下属薪资总额的递归CTE性能？

一、先补全必要索引（基础优化） #

你的表只有EmployeeID主键索引，递归查询时需要频繁通过ManagerID关联上下级，会导致大量表扫描。优先创建以下覆盖索引，避免回表查询，直接从索引获取所需数据：

CREATE NONCLUSTERED INDEX IX_Employee_ManagerID_Salary 
ON Employee (ManagerID) 
INCLUDE (Salary);

二、重构递归CTE，避免重复扫描 #

原查询的核心问题是：递归遍历所有员工后，每个员工都通过子查询重新扫描整个CTE计算下属薪资，数据量大时会造成O(N²)的时间复杂度，性能急剧下降。可以通过**反向递归（从叶子节点向上汇总）**的方式，在递归过程中直接完成薪资累加，只需要一次递归+一次聚合。

优化后的查询语句 #

WITH RecursiveSalaryCTE AS (
    -- 找出所有没有下属的叶子节点，它们的下属薪资总和为0
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        Salary,
        CAST(0 AS DECIMAL(18,2)) AS TotalSubordinateSalaries
    FROM Employee e
    WHERE NOT EXISTS (SELECT 1 FROM Employee WHERE ManagerID = e.EmployeeID)

    UNION ALL

    -- 向上递归，将当前员工的薪资+其下属的总薪资，累加到上级经理的下属薪资总和中
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        e.Salary,
        CAST(e.Salary + r.TotalSubordinateSalaries AS DECIMAL(18,2)) AS TotalSubordinateSalaries
    FROM Employee e
    INNER JOIN RecursiveSalaryCTE r ON e.EmployeeID = r.ManagerID
)
-- 聚合同一经理的所有下属薪资（避免重复计算），返回最终结果
SELECT 
    EmployeeID,
    EmployeeName,
    Salary,
    COALESCE(SUM(TotalSubordinateSalaries), 0) AS TotalSubordinateSalaries
FROM (
    SELECT DISTINCT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        Salary,
        TotalSubordinateSalaries
    FROM RecursiveSalaryCTE
) t
GROUP BY EmployeeID, EmployeeName, Salary
ORDER BY EmployeeID;

三、其他可选优化方向 #

如果你使用的是SQL Server 2019及以上版本，可以考虑使用hierarchyid类型存储员工层级关系，它天生适合树形结构查询，能进一步提升复杂层级场景下的性能：

1. 给表添加OrgNode hierarchyid字段，维护员工的层级路径
2. 基于OrgNode创建索引，直接通过层级路径快速定位下属

内容的提问来源于stack exchange，提问作者MintBerryCRUNCH