如何优化遍历父子结构的T-SQL递归CTE执行速度？

1. 优化索引，避免回表开销 #

现有IX_SponsorID非聚集索引仅包含SponsorID，查询时需要回表读取UserID，建议改成覆盖索引，直接包含查询所需字段：

CREATE NONCLUSTERED INDEX IX_SponsorID_IncludeUserID ON CCMUsers (SponsorID) INCLUDE (UserID);

覆盖索引能让数据库直接从索引文件获取所有需要的数据，无需访问聚集索引，大幅减少IO开销。

2. 强制启用并行执行计划 #

SQL Server默认对递归CTE采用单线程执行，对于40万级别的数据量，强制并行能显著提升性能：

;WITH CTE
AS
(
    -- 锚点查询
    SELECT  UserID
           ,SponsorID
           ,0 AS Level
    FROM CCMUsers 
    WHERE UserID = 45

    UNION ALL
    
    -- 递归查询
    SELECT e.UserID
          ,e.SponsorID
          ,CTE.Level + 1
    FROM CCMUsers AS e
    INNER JOIN CTE ON e.SponsorID = CTE.UserID
)
SELECT * FROM CTE
OPTION (MAXRECURSION 0, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

MAXRECURSION 0用于避免层级过深时触发递归次数限制，USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')强制优化器生成并行执行计划。

3. 替换递归CTE为集合迭代方案 #

递归CTE在处理超大数据量时性能天花板较低，可改用临时表循环迭代的方式，利用批量操作提升效率：

-- 初始化临时表，主键避免重复处理
DROP TABLE IF EXISTS #Hierarchy;
CREATE TABLE #Hierarchy (
    UserID INT,
    SponsorID INT,
    Level INT,
    PRIMARY KEY (UserID)
);

-- 插入锚点数据
INSERT INTO #Hierarchy (UserID, SponsorID, Level)
SELECT UserID, SponsorID, 0 FROM CCMUsers WHERE UserID = 45;

-- 循环遍历每一层数据
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO #Hierarchy (UserID, SponsorID, Level)
    SELECT e.UserID, e.SponsorID, h.Level + 1
    FROM CCMUsers e
    INNER JOIN #Hierarchy h ON e.SponsorID = h.UserID
    WHERE e.UserID NOT IN (SELECT UserID FROM #Hierarchy);
END

-- 查询最终结果
SELECT * FROM #Hierarchy;

这种方法通过批量处理每一层节点，比递归CTE更适合大规模数据的层级遍历。

4. 排查执行计划瓶颈 #

如果执行计划中出现大量嵌套循环或键查找，说明索引未被充分利用，优先调整索引；如果CPU占用过高，并行执行或集合迭代方案能有效缓解。

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