递归CTE性能优化求助：Azure SQL MI员工维度表层级计算

我有一个支持劳动力数据BI的星型模型，其中包含一张Type 2缓慢变化维度的员工维度表，通过单独行存储历史数据，当前约9000行数据。

需要在该表的SQL视图中新增一列，存储每位员工的管道分隔格式组织层级（上级列表）。例如：

• 员工C在t1时间向经理B汇报，B向经理A汇报，列值为A|B|C
• 员工C在t2时间向经理D汇报，D向A汇报，列值为A|D|C

我用递归CTE实现该列计算，9行模拟数据上运行正常，但实际9000行数据运行30分钟仍未返回结果，与数据量不匹配。运行环境为Azure SQL MI，表的主键[EmployeeHistoryKey]已有聚集索引。查看预估执行计划发现，查询成本主要来自[ManagerID]列的表扫描，已为[ManagerID]创建非聚集索引但性能无改善，求优化方案。

模拟数据及原查询代码 #

IF OBJECT_ID('SomeDB.some_schema.OrgHierarchyMockup', 'U') IS NOT NULL
    DROP TABLE SomeDB.some_schema.OrgHierarchyMockup
;

CREATE TABLE
    SomeDB.some_schema.OrgHierarchyMockup
(
    EmployeeHistoryKey int
    ,EmployeeID char(1)
    ,ManagerID char(1)
    ,SomeAttribute char(1)
    ,RowEffectiveDate date
    ,RowExpirationDate date
)
;

INSERT INTO
    SomeDB.some_schema.OrgHierarchyMockup
VALUES
    (1, 'a', NULL, 'x', '2023-01-01', '2023-06-01')
    ,(2, 'a', NULL, 'y', '2023-06-02', NULL)
    ,(3, 'b', 'a', 'x', '2023-01-01', '2023-06-01')
    ,(4, 'b', 'a', 'y', '2023-06-02', NULL)
    ,(5, 'c', 'a', 'x', '2023-01-01', NULL)
    ,(6, 'd', 'b', 'x', '2023-01-01', NULL)
    ,(7, 'e', 'b', 'x', '2023-01-01', '2023-06-01')
    ,(8, 'e', 'c', 'x', '2023-06-02', NULL)
    ,(9, 'f', 'c', 'x', '2023-01-01', NULL)
;

WITH
traversed_hierarchy AS
(
    SELECT --anchor member
        EmployeeHistoryKey
        ,EmployeeID
        ,ManagerID
        ,RowEffectiveDate
        ,RowExpirationDate
        ,CAST(EmployeeID AS varchar(max)) AS OrgHierarchy --this is the org hierarchy
        ,EmployeeHistoryKey AS m_EmployeeHistoryKey --necessary to "de-dupe" the result set
    FROM
        SomeDB.some_schema.OrgHierarchyMockup
    WHERE
        ManagerID IS NULL

    UNION ALL

    SELECT --recursive member
        s.EmployeeHistoryKey
        ,s.EmployeeID
        ,s.ManagerID
        ,s.RowEffectiveDate
        ,s.RowExpirationDate
        ,m.OrgHierarchy + '|' + s.EmployeeID --修正原代码笔误：OrgHierarchy1改为OrgHierarchy
        ,m.EmployeeHistoryKey
    FROM
        SomeDB.some_schema.OrgHierarchyMockup AS s --direct subordinates
    INNER JOIN --must be an inner join (not a left join) because we want the direct subordinates of the previously-fetched level in traversed_hierarchy
        traversed_hierarchy AS m
        ON
            m.EmployeeID = s.ManagerID
)
,rownumbered AS
(
    SELECT
        EmployeeHistoryKey
        ,EmployeeID
        ,ManagerID
        ,RowEffectiveDate
        ,RowExpirationDate
        ,OrgHierarchy
        ,m_EmployeeHistoryKey
        ,ROW_NUMBER() OVER( --this will allow us to de-dupe the result set
            PARTITION BY
                EmployeeHistoryKey
            ORDER BY
                m_EmployeeHistoryKey
        ) AS RowNum
    FROM
        traversed_hierarchy
)
SELECT
    EmployeeHistoryKey
    ,EmployeeID
    ,OrgHierarchy
    ,m_EmployeeHistoryKey
FROM
    rownumbered
WHERE
    RowNum = 1
ORDER BY
    EmployeeID
    ,EmployeeHistoryKey
    ,m_EmployeeHistoryKey
;

1. 修复递归CTE的时间匹配逻辑 #

原递归逻辑的核心问题是未匹配上下级的时间有效区间，导致同一员工的所有历史记录与经理的所有历史记录关联，生成大量冗余数据。必须加入时间区间判断，确保每个员工的历史记录只关联对应时间段内的经理有效记录：

-- 修正后的递归成员关联条件
SELECT --recursive member
    s.EmployeeHistoryKey
    ,s.EmployeeID
    ,s.ManagerID
    ,s.RowEffectiveDate
    ,s.RowExpirationDate
    ,m.OrgHierarchy + '|' + s.EmployeeID
    ,m.EmployeeHistoryKey
FROM
    SomeDB.some_schema.OrgHierarchyMockup AS s
INNER JOIN
    traversed_hierarchy AS m
    ON
        m.EmployeeID = s.ManagerID
        -- 关键：匹配上下级的时间有效区间
        AND s.RowEffectiveDate <= ISNULL(m.RowExpirationDate, '9999-12-31')
        AND ISNULL(s.RowExpirationDate, '9999-12-31') >= m.RowEffectiveDate

2. 创建覆盖性非聚集索引 #

单独的ManagerID索引无法覆盖递归所需的所有字段，需创建包含递归必需字段的覆盖索引，避免表扫描或键查找：

CREATE NONCLUSTERED INDEX IX_OrgHierarchyMockup_ManagerID_Effective
ON SomeDB.some_schema.OrgHierarchyMockup (ManagerID, RowEffectiveDate, RowExpirationDate)
INCLUDE (EmployeeID, EmployeeHistoryKey);

该索引按ManagerID+时间排序，同时包含递归需要的EmployeeID、EmployeeHistoryKey，让数据库可直接从索引中获取所需数据。

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

如果组织层级有明确上限（比如最多20级），可在查询末尾添加递归深度限制，避免无意义的深层递归：

-- 在查询最后添加
OPTION (MAXRECURSION 20); -- 根据实际组织层级调整数值

4. 预计算层级并缓存（推荐） #

由于Type2维度表数据不会频繁更新，可将层级计算结果预存储到持久化表中，视图直接引用缓存表，彻底解决递归性能问题：

• 创建缓存表：

CREATE TABLE SomeDB.some_schema.EmployeeHierarchyCache (
    EmployeeHistoryKey int PRIMARY KEY,
    OrgHierarchy varchar(max) NOT NULL
);

• 编写定时作业（如每日执行），重新计算并刷新缓存表
• 员工维度视图直接关联该缓存表获取层级数据

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