如何优化存储过程usp_GetReportDetails以提升数据查询速度？

问题背景 #

需优化用于SSRS报表数据集的存储过程usp_GetReportDetails，该过程基于多值逗号分隔参数筛选项目报表详情，当前处理近25000个项目时加载耗时过长，需解决以下核心问题：

• 合并不同类型（'Board Approved Plan'、'Current Plan'、'Forecast'）的预测数据到同一行
• 合并仅状态和排序方式不同的第3、4部分查询
• 精简冗余查询，提升整体性能

优化后的完整代码 #

/****** Object:  StoredProcedure [dbo].[usp_GetReportDetails] ******/
IF OBJECT_ID(N'[usp_GetReportDetails]', N'P') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_GetReportDetails]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Description : This is an SP for SSRS report dataset.
> Fetch the report details based on input filters, the input filters are multivalued-parameters (string with comma-separated values)
> Requirement is to fetch all the details for each project as a record in the output list
*/

CREATE PROCEDURE [dbo].usp_GetReportDetails 
(
    @sponsorg nvarchar(200), 
    @programname nvarchar(200), 
    @portfolio nvarchar(200), 
    @executingdep nvarchar(200), 
    @requestingdep nvarchar(200), 
    @projectmanager nvarchar(200)
)
AS
BEGIN

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @fy int=dbo.fnc_FiscalYear(getdate());

-- PART:1 获取每个项目各类型最新的QPID
-- 改用临时表定义时指定数据类型，避免默认max类型浪费资源
CREATE TABLE #pidQpidList(
    qpid INT,
    type NVARCHAR(50),
    ProjectID INT,
    ProjectCode NVARCHAR(100),  -- 根据实际业务调整长度，替代max
    ProjectName NVARCHAR(255)   -- 根据实际业务调整长度，替代max
)

INSERT INTO #pidQpidList
SELECT qpid, type, ProjectID, ProjectCode, ProjectName
FROM (
    SELECT 
        E.qpid, 
        e.Type,
        e.ProjectID,
        PM.ProjectCode,
        PM.ProjectName,
        ROW_NUMBER() OVER(PARTITION BY e.Type, e.projectid, e.fiscalyear ORDER BY w.date DESC) AS rn
    FROM AmtrakQTYPLANExtension E  
    JOIN PROJECTProjectMain PM ON PM.ProjectId = E.ProjectID AND PM.IsActive = 1 
    LEFT JOIN LibraryExecutingDepartment led ON led.ID = PM.ExecutingDepartment
    LEFT JOIN LIBRARYProjectOrganization lpo ON lpo.id = led.OrganizationID
    WHERE 
        (@Portfolio = '0' OR PM.ProjectId IN (SELECT * FROM fnGetProjectIdbyPortfolio(@Portfolio)))
        AND (@programname = '' OR ISNULL(PM.ProjectClass,0) IN (SELECT value FROM STRING_SPLIT(@programname, ',')))
        AND (@executingdep = '' OR ISNULL(lpo.id,0) IN (SELECT value FROM STRING_SPLIT(@executingdep, ',')))
        AND (@requestingdep = '' OR ISNULL(PM.RequestingDepartmentID,0) IN (SELECT value FROM STRING_SPLIT(@requestingdep, ',')))
        AND (@projectmanager = '' OR ISNULL(PM.ProjectManager,0) IN (SELECT value FROM STRING_SPLIT(@projectmanager, ','))) 
        AND EXISTS (
            SELECT 1 FROM BDGTESTBudgetEstimates be 
            WHERE be.id = e.ContractID  
              AND be.Aur_ApprovedOn IS NOT NULL  
              AND EXISTS (
                  SELECT 1 FROM [LIBRARYBudgetEstimateType] 
                  WHERE [BudgetEstimateTypeID] = be.[BudgetEstimateType] 
                    AND BudgetEstimateTypeName = 'Level 1'
              )
        )
        AND EXISTS (
            SELECT 1 FROM WorkflowFormMapping W 
            WHERE W.FormInstanceID = E.QPID  
              AND FormID = 'XQTYPLN'  
              AND w.CurrentStatus NOT IN ('Draft')
        )
) AS a
WHERE rn = 1 

-- 获取预测数据所需的月份参数
DECLARE @startmonthnumber INT, @todaymonthno INT;

SELECT @StartMonthNumber = Number 
FROM (
    SELECT DISTINCT 
        CONVERT(INT, MonthNo) AS Number,
        RIGHT(TRIM([MonthName]),4) AS [Year],  -- 简化字符串截取
        LEFT(TRIM([MonthName]),3) AS [Month]   -- 取前3位匹配月份缩写
    FROM QTYPLANCalendar
) AS A
WHERE Year = @fy-1 AND Month = 'Oct'

SELECT @todaymonthno = MonthNo 
FROM QTYPLANCalendar 
WHERE CAST(date AS DATE) = CAST(GETDATE() AS DATE)

-- PART : 2 合并不同类型预测数据到同一行
CREATE TABLE #forecastlevel1(
    ProjectID INT,
    fyBoardPlanned NUMERIC(18,2),
    fyPlanned NUMERIC(18,2),
    fyActuals NUMERIC(18,2),
    fyRemForecast NUMERIC(18,2)
)

INSERT INTO #forecastlevel1
SELECT
    l.ProjectID,
    CAST(SUM(CASE WHEN l.type='Board Approved Plan' AND sd.ScheduleID BETWEEN @StartMonthNumber AND @startmonthnumber+11 THEN sd.Quantity END) AS NUMERIC(18,2)) AS fyBoardPlanned,
    CAST(SUM(CASE WHEN l.type='Current Plan' AND sd.ScheduleID BETWEEN @StartMonthNumber AND @startmonthnumber+11 THEN sd.Quantity END) AS NUMERIC(18,2)) AS fyPlanned,
    CAST(SUM(CASE WHEN l.type='Forecast' AND sd.ScheduleID BETWEEN @StartMonthNumber AND @todaymonthno THEN sd.Quantity END) AS NUMERIC(18,2)) AS fyActuals,
    CAST(SUM(CASE WHEN l.type='Forecast' AND sd.ScheduleID BETWEEN @todaymonthno AND @startmonthnumber+11 THEN sd.Quantity END) AS NUMERIC(18,2)) AS fyRemForecast
FROM QTYPLANItemScheduleData sd
JOIN QTYPLANMaster q WITH(NOLOCK) ON q.QPID = sd.QPID 
INNER JOIN #pidQpidList l ON q.ProjectId = l.ProjectID AND sd.QPID = l.qpid
-- 移除无关联的CORITEMItemDetails表（原查询未使用其字段）
GROUP BY l.ProjectID

-- PART : 3+4 合并获取最早L1历史和最新L1审批记录
WITH budgetRecords AS (
    SELECT 
        BE.PID,
        -- 最早L1历史记录
        FIRST_VALUE(BE.EstimateTotal) OVER(PARTITION BY BE.PID ORDER BY wf.date) AS L1HistoryAmount,
        -- 最新L1审批记录
        FIRST_VALUE(BE.EstimateTotal) OVER(PARTITION BY BE.PID ORDER BY wf.date DESC) AS L1ApprovedAmount
    FROM BDGTESTBudgetEstimates BE 
    JOIN WorkflowFormMapping WF ON BE.ID = WF.FormInstanceID AND FormID='BDGTEST'
    WHERE WF.CurrentStatus IN ('Level 1 History', 'Level 1 Approved')
),
-- 去重每个项目的记录
distinctBudgetRecords AS (
    SELECT DISTINCT PID, L1HistoryAmount, L1ApprovedAmount
    FROM budgetRecords
),
-- PART : 5 已审批费用总和
approvedExpenses AS(
    SELECT  
        Ex.PID, 
        SUM(Ex.TotCost) AS TotalAmount
    FROM vw_AMTRAK_EXPSFRMExpenseList Ex 
    JOIN WorkflowFormMapping WF ON Ex.EFID = WF.FormInstanceID AND FormID='EXPSFRM'
    WHERE WF.CurrentStatus='Approved' 
    GROUP BY Ex.PID
)

-- PART : 6 主查询整合所有数据
SELECT 
    PM.ProjectId,
    PM.ProjectCode,
    PM.ProjectName,
    ISNULL(BE.L1HistoryAmount, BE.L1ApprovedAmount) AS LOPBoardPlanned,
    BE.L1ApprovedAmount AS LOPPlanned,
    Ex.TotalAmount AS LOPActuals,
    CASE 
        WHEN BE.L1ApprovedAmount IS NULL AND Ex.TotalAmount IS NULL THEN NULL 
        ELSE ISNULL(BE.L1ApprovedAmount,0.00) - ISNULL(Ex.TotalAmount,0.00) 
    END AS LOPRemainingCost,
    CASE 
        WHEN BE.L1ApprovedAmount IS NULL THEN NULL
        WHEN (BE.L1HistoryAmount IS NULL AND ISNULL(BE.L1ApprovedAmount,0.00)=0) OR (BE.L1HistoryAmount=0) THEN NULL 
        WHEN BE.L1HistoryAmount IS NULL THEN 0.00
        ELSE BE.L1ApprovedAmount - BE.L1HistoryAmount 
    END AS LOPBudgetVariance,
    fr.fyBoardPlanned AS FYBoardPlanned,
    fr.fyPlanned AS FYPlanned,
    fr.fyActuals AS FYActuals,
    fr.fyRemForecast AS FYRemainingForecast,
    ISNULL(fr.fyPlanned,0.00) - (ISNULL(fr.fyActuals,0.00) + ISNULL(fr.fyRemForecast,0.00)) AS FYBudgetVariance
FROM (SELECT DISTINCT ProjectID, ProjectCode, ProjectName FROM #pidQpidList) PM 
LEFT JOIN distinctBudgetRecords BE ON BE.PID = PM.ProjectId
LEFT JOIN approvedExpenses Ex ON Ex.PID = PM.ProjectId
LEFT JOIN #forecastlevel1 fr ON fr.ProjectId = PM.ProjectId

DROP TABLE #pidQpidList, #forecastlevel1

END

核心优化说明 #

1. 预测数据合并到同一行 #

• 原PART2按type分组生成3条/项目的记录，改为直接在SUM中按type做条件判断，GROUP BY仅保留ProjectID，每个项目输出一行包含所有类型的预测数据
• 简化月份范围判断：将sd.ScheduleID > @StartMonthNumber-1 AND sd.ScheduleID < @startmonthnumber+12改为BETWEEN @StartMonthNumber AND @startmonthnumber+11，逻辑等价但更清晰
• 移除未使用的CORITEMItemDetails关联，减少JOIN开销

2. 合并第3、4部分查询 #

• 使用FIRST_VALUE()窗口函数，在同一查询中同时获取每个项目最早的L1历史记录（按date升序取第一条）和最新的L1审批记录（按date降序取第一条）
• 通过DISTINCT去重每个项目的结果，替代原两次独立的ROW_NUMBER子查询，减少一次全表扫描

3. 整体性能提升 #

• 临时表定义时指定合适的字段长度（如ProjectCode NVARCHAR(100)替代NVARCHAR(MAX)），减少内存占用
• 将原PART1中的多个JOIN改为EXISTS子查询，避免不必要的字段投影，优化执行计划
• 简化字符串截取逻辑：用RIGHT()和LEFT()替代复杂的SUBSTRING计算，提升效率
• 主查询中仅需一次JOIN关联预测数据临时表，替代原三次LEFT JOIN，减少关联开销

内容的提问来源于stack exchange，提问作者59_Varun C U