CTE执行过慢求优化：获取无限层级顶层父节点高效方案

问题背景 #

现有#master_table和#child_table两张表，需获取每个节点的最顶层父节点的tracking_number与source_type，父/子层级支持无限嵌套。当前使用递归CTE实现逻辑，但处理3万行含多层级子节点的数据集时，执行耗时约45秒，性能不佳，寻求更高效的替代方案。

示例表 #

#master_table #

#child_table #

预期结果 #

当前递归CTE代码 #

; WITH data_cte AS (
    SELECT 
        a.master_id,
        b.child_id, 
        '' as parent_tracking_number,
        b.source_type as parent_type
    FROM 
        #master_table as a with(nolock)
        inner join #child_table as b with(nolock) on b.master_id = a.master_id
    WHERE 
        b.parent_id is null

    UNION ALL

    SELECT 
        a.master_id, 
        b.child_id, 
        e.tracking_number as parent_tracking_number,
        e.parent_type
    FROM 
        #master_table as a with(nolock)
        inner join #child_table as b with(nolock) on b.master_id = a.master_id
        inner join data_cte as e on b.parent_id = e.child_id            
)

SELECT * FROM data_cte

优化方案 #

1. 索引优化（立竿见影的基础优化） #

递归CTE性能差的核心是递归过程中反复查询无索引的关联字段，优先创建以下索引：

• 给#child_table的parent_id建非聚集覆盖索引，包含关联和返回所需字段，避免回表
• 给#child_table的child_id建唯一索引，确保节点关联时快速定位
• 给#child_table的master_id建索引，优化与#master_table的关联

CREATE NONCLUSTERED INDEX IX_ChildTable_ParentId ON #child_table (parent_id)
INCLUDE (child_id, master_id, tracking_number, source_type);

CREATE UNIQUE NONCLUSTERED INDEX IX_ChildTable_ChildId ON #child_table (child_id);

CREATE NONCLUSTERED INDEX IX_ChildTable_MasterId ON #child_table (master_id);

2. 改写递归CTE逻辑（减少冗余关联） #

原CTE每次递归都关联#master_table，但#child_table本身已包含master_id，可直接从#child_table取数，同时修正顶层节点的父信息赋值错误：

; WITH data_cte AS (
    SELECT 
        master_id,
        child_id, 
        tracking_number as parent_tracking_number,
        source_type as parent_type
    FROM 
        #child_table with(nolock)
    WHERE 
        parent_id is null

    UNION ALL

    SELECT 
        b.master_id, 
        b.child_id, 
        e.parent_tracking_number,
        e.parent_type
    FROM 
        #child_table as b with(nolock)
        INNER JOIN data_cte as e on b.parent_id = e.child_id            
)

SELECT * FROM data_cte

3. 迭代法替代递归CTE（适合深度嵌套场景） #

递归CTE的栈开销随层级增加而飙升，迭代法通过循环更新临时表，逐步传递顶层父节点信息，性能更稳定：

-- 初始化临时表，存储所有节点及初始父信息
SELECT 
    master_id,
    child_id,
    tracking_number as parent_tracking_number,
    source_type as parent_type,
    parent_id
INTO #temp_nodes
FROM #child_table with(nolock);

-- 循环更新子节点的顶层父信息，直到无更新
WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE t
    SET 
        t.parent_tracking_number = p.parent_tracking_number,
        t.parent_type = p.parent_type
    FROM #temp_nodes t
    INNER JOIN #temp_nodes p ON t.parent_id = p.child_id
    WHERE t.parent_tracking_number != p.parent_tracking_number
END

-- 输出结果
SELECT master_id, child_id, parent_tracking_number, parent_type FROM #temp_nodes;

DROP TABLE #temp_nodes;

4. 预计算缓存顶层父信息（非实时场景最优） #

如果数据不是实时更新，可定期预计算所有节点的顶层父信息并缓存，查询时直接读取：

-- 创建缓存表
CREATE TABLE #node_top_parent (
    child_id VARCHAR(10) PRIMARY KEY,
    parent_tracking_number VARCHAR(10),
    parent_type VARCHAR(20)
);

-- 初始化顶层节点缓存
INSERT INTO #node_top_parent (child_id, parent_tracking_number, parent_type)
SELECT child_id, tracking_number, source_type
FROM #child_table with(nolock)
WHERE parent_id IS NULL;

-- 循环填充子节点的顶层父信息
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO #node_top_parent (child_id, parent_tracking_number, parent_type)
    SELECT b.child_id, p.parent_tracking_number, p.parent_type
    FROM #child_table b with(nolock)
    LEFT JOIN #node_top_parent p ON b.parent_id = p.child_id
    LEFT JOIN #node_top_parent existing ON b.child_id = existing.child_id
    WHERE existing.child_id IS NULL AND p.child_id IS NOT NULL;
END

-- 查询时关联缓存表
SELECT 
    a.master_id,
    b.child_id,
    p.parent_tracking_number,
    p.parent_type
FROM #master_table a with(nolock)
INNER JOIN #child_table b with(nolock) ON a.master_id = b.master_id
INNER JOIN #node_top_parent p ON b.child_id = p.child_id;

DROP TABLE #node_top_parent;

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