递归存储过程处理员工层级数据的性能优化问询

问题背景 #

存储过程GetCombinedRequestInfosByEmail用于获取指定员工下属的所有请求信息，低层员工场景执行高效，但针对管理30000名员工的CEO时，耗时约55秒，远慢于小团队经理的毫秒级执行速度。

原逻辑通过递归CTE获取下属层级，再用游标循环调用GetRequestInfosByUser存储过程，逐个员工查询请求数据——这是CEO场景性能瓶颈的核心原因：循环30000次调用存储过程，每次都要编译执行动态SQL，累计开销极大。

原代码与表结构 #

GetCombinedRequestInfosByEmail原代码 #

ALTER PROCEDURE GetCombinedRequestInfosByEmail
    @Email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #EmployeeHierarchy 
    (
        employee_id NVARCHAR(50) PRIMARY KEY,
        employee_email NVARCHAR(255),
        level INT
    );

    WITH EmployeeHierarchy AS 
    (
        SELECT 
            u.userid AS employee_id, 
            u.Email AS employee_email,
            u.manageruserid AS manager_id,
            0 AS level
        FROM 
            UserTable u
        WHERE 
            u.Email = @Email

        UNION ALL

        SELECT 
            u.userid AS employee_id, 
            u.Email AS employee_email,
            u.manageruserid AS manager_id,
            eh.level + 1
        FROM 
            UserTable u
        JOIN 
            EmployeeHierarchy eh ON u.manageruserid = eh.employee_id
    )
    INSERT INTO #EmployeeHierarchy (employee_id, employee_email, level)
        SELECT DISTINCT
            employee_id, employee_email, level
        FROM 
            EmployeeHierarchy 
        WHERE 
            level > 0
        ORDER BY 
            level, employee_id;

    CREATE TABLE #RequestInfodata 
    (
        RequestID INT,
        RequestCreatedByEmailID NVARCHAR(200),
        ApplicationName NVARCHAR(200),
        ITowner NVARCHAR(100),
        Businessowner NVARCHAR(100),
        RiskScore INT,
        RequestCreatedBy NVARCHAR(24)
    );

    DECLARE @employee_id NVARCHAR(50);
    DECLARE @employee_email NVARCHAR(255);

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_email
        FROM #EmployeeHierarchy;

    OPEN employee_cursor;

    FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_email;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #RequestInfodata
            EXEC GetRequestInfosByUser 
                    @UserId = @employee_id, 
                    @UserEmail = @employee_email,        
                    @IsBusinessOrItOwner = 1;

        FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_email;
    END

    CLOSE employee_cursor;
    DEALLOCATE employee_cursor;
    
    SELECT DISTINCT * 
    FROM #RequestInfodata;

    -- Clean up temporary tables
    DROP TABLE #EmployeeHierarchy;
    DROP TABLE #RequestInfodata;
END;

GetRequestInfosByUser原代码 #

ALTER PROCEDURE [dbo].[GetRequestInfosByUser]
    @UserId VARCHAR(50) = NULL,
    @UserEmail VARCHAR(50) = NULL,
    @IsBusinessOrItOwner BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- Base SQL query
    DECLARE @SQL NVARCHAR(MAX) = '

    SELECT
        ri.RequestID,
        ri.RequestCreatedByEmailID,
        ri.NameOftheTool AS ApplicationName,
        ri.ITOwners AS ITowner,
        ri.BusinessOwners AS Businessowner,
        ri.RiskSummaryScore AS RiskScore,
        ri.RequestCreatedBy,
    FROM 
        RequestInfo ri
    LEFT JOIN 
        (SELECT 
             *,
             ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY DateModified DESC) AS RowNumber
         FROM 
             WorkflowTable) AS wf ON ri.RequestID = wf.RequestID 
                                  AND wf.RowNumber = 1
    WHERE 1 = 1 ';

    -- Append conditions to the WHERE clause based on input parameters
    IF @UserId IS NOT NULL AND @UserId <> 'ALL' AND @IsBusinessOrItOwner = 0
    BEGIN
        SET @SQL = @SQL + ' AND ri.RequestCreatedBy = @UserId';
    END
    ELSE IF @UserId IS NOT NULL AND @UserEmail IS NOT NULL AND @IsBusinessOrItOwner = 1
    BEGIN
        SET @SQL = @SQL + ' AND (ri.RequestCreatedBy = @UserId OR ri.ITOwners = @UserEmail OR   
ri.BusinessOwners = @UserEmail)';
    END

    -- Execute the SQL query
    EXEC sp_executesql @SQL, N'@UserId VARCHAR(50), @UserEmail VARCHAR(50)', @UserId, @UserEmail;
END;

相关表结构 #

UserTable

RequestInfo

预期输出（CEO场景） #

优化方案 #

1. 移除游标与循环调用，改用批量查询 #

彻底去掉游标循环，将层级查询与请求查询合并为单次关联查询，避免30000次存储过程调用的开销。

2. 重写存储过程，合并逻辑 #

优化后的GetCombinedRequestInfosByEmail直接完成所有逻辑，无需依赖GetRequestInfosByUser：

ALTER PROCEDURE GetCombinedRequestInfosByEmail
    @Email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 递归获取所有下属员工（排除当前用户）
    WITH EmployeeHierarchy AS 
    (
        SELECT 
            u.userid AS employee_id, 
            u.Email AS employee_email,
            0 AS level
        FROM 
            UserTable u
        WHERE 
            u.Email = @Email

        UNION ALL

        SELECT 
            u.userid AS employee_id, 
            u.Email AS employee_email,
            eh.level + 1
        FROM 
            UserTable u
        JOIN 
            EmployeeHierarchy eh ON u.manageruserid = eh.employee_id
    )
    -- 一次性关联获取所有符合条件的请求数据
    SELECT DISTINCT
        ri.RequestID,
        ri.RequestCreatedByEmailID,
        ri.NameOftheTool AS ApplicationName,
        ri.ITOwners AS ITowner,
        ri.BusinessOwners AS Businessowner,
        ri.RiskSummaryScore AS RiskScore,
        ri.RequestCreatedBy
    FROM 
        RequestInfo ri
    LEFT JOIN 
        (SELECT 
             *,
             ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY DateModified DESC) AS RowNumber
         FROM 
             WorkflowTable) AS wf ON ri.RequestID = wf.RequestID 
                                  AND wf.RowNumber = 1
    INNER JOIN 
        EmployeeHierarchy eh ON (ri.RequestCreatedBy = eh.employee_id 
                                OR ri.ITOwners = eh.employee_email 
                                OR ri.BusinessOwners = eh.employee_email)
    WHERE 
        eh.level > 0; -- 只取下属数据，排除当前用户
END;

3. 索引优化 #

添加以下索引进一步提升查询效率：

• UserTable：加速递归层级查询

  CREATE NONCLUSTERED INDEX IX_UserTable_ManagerUserID ON UserTable(manageruserid) INCLUDE(userid, Email);
  

• RequestInfo：覆盖查询所需的筛选和返回列，避免键查找

  CREATE NONCLUSTERED INDEX IX_RequestInfo_CreatedBy_Owners ON RequestInfo(RequestCreatedBy, ITOwners, BusinessOwners)
  INCLUDE(RequestID, RequestCreatedByEmailID, NameOftheTool, RiskSummaryScore);
  

• WorkflowTable：加速ROW_NUMBER()排序逻辑

  CREATE NONCLUSTERED INDEX IX_WorkflowTable_RequestID_DateModified ON WorkflowTable(RequestID, DateModified DESC);
  

4. 其他优化细节 #

• 移除原代码中的临时表，避免数据插入和读取的额外开销
• 若RequestID是RequestInfo表的主键，可移除DISTINCT进一步提升性能
• 去掉动态SQL的使用，改用静态参数化查询，避免重复编译SQL语句

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