递归SQL查询性能优化求助：层级数据查询瓶颈解决

算力阿峰

2026-6-15

SQL递归查询优化挑战

我的数据为层级树形结构，最大深度为20层：

Root -> Node1 -> Node2 -> ... -> Node20

目前在递归查询优化上遇到瓶颈，具体问题：

递归深度增加时执行时间大幅变长
难以在查询可读性与性能之间取得平衡
需要方案能支持大规模数据集扩展，求高效处理递归SQL查询的最佳实践。

当前使用的SQL函数代码

1. 获取关系GUID的函数

CREATE FUNCTION [dbo].[REPORTGetHierarchyRelations] 
(
    @RelationTypeName VARCHAR(50)
)
RETURNS @LocalTable TABLE 
(
    RelationGUID UNIQUEIDENTIFIER PRIMARY KEY
)
AS
BEGIN
    -- 预定义RelationTypeName的映射
    INSERT INTO @LocalTable
    SELECT DISTINCT rdef.RelationGUID 
    FROM [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].IJRelationdef rdef
    JOIN [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].JRelation_Has_JRelationCollections r1 ON r1.oidorg = rdef.oid
    JOIN [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].IJRelationCollectionDef rcdef ON rcdef.oid = r1.oiddst
    WHERE 
        rcdef.isorigin = 1 
        AND (
            (@RelationTypeName = 'SystemTree' OR @RelationTypeName = 'SystemHierarchy' AND rcdef.Interfaceiid = '524AAFA0-1EBE-4977-9249-41627C94F5F3') OR
            (@RelationTypeName = 'AssemblyHierarchy' AND rcdef.Interfaceiid = '07AE45B9-D88F-4DF2-865D-FEBE67D82E32')
        );

    -- 处理固定RelationTypeName值
    INSERT INTO @LocalTable (RelationGUID)
    SELECT 
        CASE 
            WHEN @RelationTypeName = 'SpaceHierarchy' THEN '44D74F1B-895E-49E6-B1A5-F87623B8D412'
            WHEN @RelationTypeName = 'FolderHierarchy' THEN '9CB8A6A1-0186-11D3-A61C-080036528403'
            WHEN @RelationTypeName = 'WBSHierarchy' THEN '85BE1FF0-E8E9-4DF3-A93A-7507E1EF0A15'
            WHEN @RelationTypeName = 'Hierarchy' THEN '7FAA6153-07BE-11D2-BC6B-0800360DCD02'
            WHEN @RelationTypeName = 'FilterFolderHierarchy' THEN '38461284-82EC-11D4-9D3E-00104B34EBF6'
            WHEN @RelationTypeName = 'DrawingFolderHierarchy' THEN '8E645EFF-904C-4896-9D75-20047C9F2379'
            ELSE NULL
        END
    WHERE @RelationTypeName IN (
        'SpaceHierarchy', 
        'FolderHierarchy', 
        'WBSHierarchy', 
        'Hierarchy', 
        'FilterFolderHierarchy', 
        'DrawingFolderHierarchy'
    );

    RETURN;
END;
GO

2. 递归获取子节点的函数

ALTER FUNCTION [dbo].[REPORTGetAllChildrenInHierarchyByOID] 
(
    @oid UNIQUEIDENTIFIER, 
    @RelationTypeName VARCHAR(50)
)
RETURNS @LocalTable TABLE 
(
    oidParent UNIQUEIDENTIFIER,
    oidChild UNIQUEIDENTIFIER PRIMARY KEY
)
AS
BEGIN
    -- CTE处理层级遍历
    WITH HierarchyTree AS
    (
        -- 锚点：选择给定父节点的直接子节点
        SELECT 
            rrv.idOrigin AS ParentID, 
            rrv.idDestination AS ChildID, 
            0 AS GenerationLevel
        FROM 
            [SP\SPSQL].[BASRAH2_MDB].[dbo].CoreRelations rrv
        WHERE 
            rrv.idOrigin = @oid
            AND rrv.RelationType IN (SELECT RelationGUID 
                                     FROM  [dbo].REPORTGetHierarchyRelations(@RelationTypeName))
        
        UNION ALL
        
        -- 递归：深入遍历层级
        SELECT 
            rrv.idOrigin AS ParentID, 
            rrv.idDestination AS ChildID, 
            H.GenerationLevel + 1
        FROM 
            [SP\SPSQL].[BASRAH2_MDB].[dbo].CoreRelations rrv
        INNER JOIN 
            HierarchyTree H ON rrv.idOrigin = H.ChildID
        WHERE 
            rrv.RelationType IN (SELECT RelationGUID 
                                 FROM [dbo].REPORTGetHierarchyRelations(@RelationTypeName))
    )
    -- 将结果插入输出表
    INSERT INTO @LocalTable (oidParent, oidChild)
        SELECT ParentID, ChildID
        FROM HierarchyTree;

    RETURN;
END;
GO

优化实践建议

1. 消除递归中的重复函数调用

当前递归CTE每次迭代都会调用REPORTGetHierarchyRelations，造成大量重复计算。先将关系GUID缓存到变量，避免重复执行：

ALTER FUNCTION [dbo].[REPORTGetAllChildrenInHierarchyByOID] 
(
    @oid UNIQUEIDENTIFIER, 
    @RelationTypeName VARCHAR(50)
)
RETURNS @LocalTable TABLE 
(
    oidParent UNIQUEIDENTIFIER,
    oidChild UNIQUEIDENTIFIER PRIMARY KEY
)
AS
BEGIN
    -- 缓存关系GUID，避免递归中重复调用函数
    DECLARE @RelationGuids TABLE (RelationGUID UNIQUEIDENTIFIER PRIMARY KEY)
    INSERT INTO @RelationGuids
    SELECT RelationGUID FROM [dbo].REPORTGetHierarchyRelations(@RelationTypeName)

    WITH HierarchyTree AS
    (
        SELECT 
            rrv.idOrigin AS ParentID, 
            rrv.idDestination AS ChildID, 
            0 AS GenerationLevel
        FROM 
            [SP\SPSQL].[BASRAH2_MDB].[dbo].CoreRelations rrv
        WHERE 
            rrv.idOrigin = @oid
            AND rrv.RelationType IN (SELECT RelationGUID FROM @RelationGuids)
        
        UNION ALL
        
        SELECT 
            rrv.idOrigin AS ParentID, 
            rrv.idDestination AS ChildID, 
            H.GenerationLevel + 1
        FROM 
            [SP\SPSQL].[BASRAH2_MDB].[dbo].CoreRelations rrv
        INNER JOIN 
            HierarchyTree H ON rrv.idOrigin = H.ChildID
        WHERE 
            rrv.RelationType IN (SELECT RelationGUID FROM @RelationGuids)
    )
    INSERT INTO @LocalTable (oidParent, oidChild)
        SELECT ParentID, ChildID
        FROM HierarchyTree;

    RETURN;
END;
GO

2. 优化索引策略

给CoreRelations表创建覆盖索引，直接满足递归查询的字段需求：

CREATE NONCLUSTERED INDEX IX_CoreRelations_Origin_RelationType 
ON [SP\SPSQL].[BASRAH2_MDB].[dbo].CoreRelations (idOrigin, RelationType) 
INCLUDE (idDestination);

给IJRelationCollectionDef表创建复合索引，加速筛选逻辑：

CREATE NONCLUSTERED INDEX IX_IJRelationCollectionDef_Interface_IsOrigin 
ON [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].IJRelationCollectionDef (Interfaceiid, isorigin)
INCLUDE (oid);

3. 替换多语句表值函数为内联表值函数

多语句表值函数的优化效率远低于内联表值函数，重构REPORTGetHierarchyRelations：

CREATE OR ALTER FUNCTION [dbo].[REPORTGetHierarchyRelations] 
(
    @RelationTypeName VARCHAR(50)
)
RETURNS TABLE
AS
RETURN (
    -- 原查询部分
    SELECT DISTINCT rdef.RelationGUID 
    FROM [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].IJRelationdef rdef
    JOIN [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].JRelation_Has_JRelationCollections r1 ON r1.oidorg = rdef.oid
    JOIN [SP\SPSQL].[BASRAH2_CDB_SCHEMA].[dbo].IJRelationCollectionDef rcdef ON rcdef.oid = r1.oiddst
    WHERE 
        rcdef.isorigin = 1 
        AND (
            (@RelationTypeName IN ('SystemTree', 'SystemHierarchy') AND rcdef.Interfaceiid = '524AAFA0-1EBE-4977-9249-41627C94F5F3') OR
            (@RelationTypeName = 'AssemblyHierarchy' AND rcdef.Interfaceiid = '07AE45B9-D88F-4DF2-865D-FEBE67D82E32')
        )
    -- 合并固定值部分
    UNION ALL
    SELECT 
        CASE 
            WHEN @RelationTypeName = 'SpaceHierarchy' THEN '44D74F1B-895E-49E6-B1A5-F87623B8D412'
            WHEN @RelationTypeName = 'FolderHierarchy' THEN '9CB8A6A1-0186-11D3-A61C-080036528403'
            WHEN @RelationTypeName = 'WBSHierarchy' THEN '85BE1FF0-E8E9-4DF3-A93A-7507E1EF0A15'
            WHEN @RelationTypeName = 'Hierarchy' THEN '7FAA6153-07BE-11D2-BC6B-0800360DCD02'
            WHEN @RelationTypeName = 'FilterFolderHierarchy' THEN '38461284-82EC-11D4-9D3E-00104B34EBF6'
            WHEN @RelationTypeName = 'DrawingFolderHierarchy' THEN '8E645EFF-904C-4896-9D75-20047C9F2379'
            ELSE NULL
        END AS RelationGUID
    WHERE @RelationTypeName IN (
        'SpaceHierarchy', 
        'FolderHierarchy', 
        'WBSHierarchy', 
        'Hierarchy', 
        'FilterFolderHierarchy', 
        'DrawingFolderHierarchy'
    )
);
GO

4. 限制递归深度（可选）

在CTE中添加层级限制，避免意外的无限递归，同时减少不必要的迭代：

-- 在递归部分的WHERE条件中添加
AND H.GenerationLevel < 19 -- 初始层级为0，限制到19即最大20层

5. 预计算层级结构（大规模场景）

如果层级结构不频繁变更，可预计算并存储节点的路径或层级信息，比如添加Path字段（如Root\Node1\Node2）或Level字段，查询时直接基于预计算字段筛选，避免实时递归。

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

递归SQL查询性能优化求助：层级数据查询瓶颈解决

当前使用的SQL函数代码 #

1. 获取关系GUID的函数 #

2. 递归获取子节点的函数 #

优化实践建议 #

1. 消除递归中的重复函数调用 #

2. 优化索引策略 #

3. 替换多语句表值函数为内联表值函数 #

4. 限制递归深度（可选） #

5. 预计算层级结构（大规模场景） #