如何优化SQL Server中多层自连接的层级查询

问题背景 #

有一张最多支持5级层级的表，需根据issue_id和parent_id生成两个汇总字段：

• parent_desc：当前节点实际最顶层父级的summary
• succeed_desc：当前节点直接父级的summary

原实现通过5次LEFT JOIN自连接完成，但查询耗时近250秒，需优化。

原查询代码 #

WITH  cte_a AS
(
    SELECT
        'AON property' AS summary, 1001 AS issue_id, 2001 AS parent_id
    UNION ALL
    SELECT 'AON property L1', 2001, 3001
    UNION ALL
    SELECT 'AON Property L2', 3001, 4001
    UNION ALL
    SELECT 'AON Property L3', 4001, NULL
    UNION ALL
    SELECT 'LONG CHAIN CLUBS', 1002, 2222
    UNION ALL
    SELECT 'LONG CHAIN L1', 2222, 3003
    UNION ALL
    SELECT 'LONG CHAIN L2', 3003, NULL
)
SELECT
    a.*,
    CASE 
        WHEN f.summary IS NOT NULL THEN e.summary
        WHEN e.summary IS NOT NULL THEN d.summary
        WHEN d.summary IS NOT NULL THEN c.summary
        WHEN c.summary IS NOT NULL THEN b.summary
        WHEN b.summary IS NOT NULL THEN a.summary 
    END AS succeed_desc,
    COALESCE (f.summary, e.summary, d.summary, c.summary, b.summary, a.summary) AS parent_desc
FROM
    cte_a a 
LEFT JOIN 
    cte_a b ON a.parent_id = b.issue_id --Level1
LEFT JOIN 
    cte_a c ON b.parent_id = c.issue_id --Level2
LEFT JOIN 
    cte_a d ON c.parent_id = d.issue_id --Level3
LEFT JOIN 
    cte_a e ON d.parent_id = e.issue_id --Level4
LEFT JOIN 
    cte_a f ON e.parent_id = f.issue_id --Level5

优化方案 #

1. 用递归CTE替代多次自连接

递归CTE是SQL Server处理层级数据的原生高效方案，避免了多次自连接带来的表扫描和冗余计算。通过递归遍历每个节点的所有父级，记录层级深度后，直接匹配所需字段：

WITH cte_hierarchy AS (
    -- 锚点成员：初始节点，记录当前层级为0
    SELECT 
        issue_id,
        parent_id,
        summary,
        summary AS current_summary,
        parent_id AS current_parent_id,
        0 AS level,
        summary AS top_parent_desc,
        summary AS direct_parent_desc
    FROM cte_a
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归成员：向上遍历父节点，更新层级和父级信息
    SELECT 
        child.issue_id,
        child.parent_id,
        child.summary,
        parent.current_summary,
        parent.current_parent_id,
        parent.level + 1 AS level,
        parent.top_parent_desc,
        parent.current_summary AS direct_parent_desc
    FROM cte_a child
    INNER JOIN cte_hierarchy parent ON child.parent_id = parent.issue_id
)
-- 最终查询：匹配每个节点的top_parent_desc（parent_desc）和direct_parent_desc（succeed_desc）
SELECT
    issue_id,
    parent_id,
    summary,
    direct_parent_desc AS succeed_desc,
    top_parent_desc AS parent_desc
FROM cte_hierarchy
ORDER BY issue_id;

2. 添加针对性索引

如果是生产环境的物理表，在issue_id和parent_id上创建包含summary的复合索引，彻底消除表扫描和书签查找：

CREATE NONCLUSTERED INDEX IX_IssueHierarchy ON YourActualTableName (issue_id, parent_id)
INCLUDE (summary);

3. 简化字段计算逻辑

递归CTE中直接记录direct_parent_desc（直接父级）和top_parent_desc（最顶层父级），避免原查询中嵌套CASE和COALESCE的复杂判断，进一步降低计算开销。

优化效果 #

递归CTE仅需遍历层级数据一次，相比5次自连接的高复杂度，时间复杂度降至线性级别，配合索引后查询耗时可大幅降低到毫秒级，且结果与原查询完全一致。

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