SQL Server递归查询构建层级路径过慢如何优化？

原查询执行极慢是几个核心逻辑错误叠加导致的：

• 锚点逻辑完全错误：原查询锚点选取了所有行的Manager作为递归根节点，相当于将每个中间管理者都作为顶层节点重复遍历整棵组织架构树，4万行数据会产生数万次重复递归计算，生成天量冗余中间结果。
• 缺失支撑递归关联的索引：递归Join条件为e2.Manager = emp.Employee，无索引情况下每次递归迭代都要对4万行的表做全表扫描，迭代层数越多扫描开销呈指数级上升。
• 不必要的类型转换开销：递归全程使用varchar(max)类型存储路径，且每层递归都做重复类型转换，处理效率远低于适配长度的varchar类型；原代码还存在字段拼写错误（Manger漏写a），额外增加了字段处理开销。
• 无递归终止保护：如果表中存在循环汇报的脏数据，会触发无限递归，进一步拖慢执行速度。

1. 先建递归专用覆盖索引 #

递归关联逻辑完全依赖Manager字段关联上级Employee，建覆盖索引可以让每次Join直接走索引查找，彻底避免全表扫描：

CREATE NONCLUSTERED INDEX IX_EMPLOYEE_Manager_INC_Employee 
ON EMPLOYEE(Manager) 
INCLUDE(Employee);

2. 修正递归CTE逻辑 #

将锚点改为仅选取真正的顶层根节点（Manager为NULL的最高级员工），从根节点向下一次遍历完所有层级，避免重复计算；去掉冗余类型转换，增加层级字段做递归保护：

WITH emp AS (
    -- 锚点：仅取顶层根节点
    SELECT
        Employee,
        Manager,
        CAST(Employee AS VARCHAR(1000)) AS Path,
        0 AS Level
    FROM EMPLOYEE
    WHERE Manager IS NULL

    UNION ALL

    SELECT
        e.Employee,
        e.Manager,
        CAST(emp.Path + ' - ' + e.Employee AS VARCHAR(1000)) AS Path,
        emp.Level + 1 AS Level
    FROM EMPLOYEE e
    INNER JOIN emp 
        ON e.Manager = emp.Employee
    WHERE emp.Level < 100 -- 循环保护，根据实际组织架构最大层级调整
)
SELECT Employee, Manager, Path
FROM emp
OPTION (MAXRECURSION 100); -- 和上面的层级阈值保持一致，不要随意设为0（无递归深度限制）

注：varchar长度根据实际员工名长度和最大层级调整，比如单员工名最长20字符、最大层级20层，设为varchar(500)完全足够，比varchar(max)处理效率高30%以上。

3. 高频查询场景持久化结果 #

如果该查询需要频繁执行，不要每次都实时递归计算：

• 给EMPLOYEE表新增Path列，第一次用上面的优化脚本初始化所有路径值
• 后续仅当员工汇报关系调整时，同步更新该员工及其所有下属的Path字段即可，日常查询直接读表字段，无需递归，毫秒级返回结果。

4. 提前清理脏数据 #

执行查询前先排查两类脏数据，避免递归做无用功：

• 循环汇报：比如A的上级是B，B的上级是A，会触发递归死循环
• 孤立节点：员工填写的Manager不存在于Employee字段的有效值中，会导致路径断裂

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