如何优化SQL Server查询避免超时？按区域计算阶段平均时长

现有环境里有视图[stages].[jobStages]，包含JobNumber、Region以及各任务阶段的完成日期；基于这个视图又建了[forecast].[DurationTable]，用来存各阶段的间隔时长（比如Duration1 = Stage2 - Stage1）。

现在需要创建新视图，按Region计算各阶段的平均间隔时长，但只统计该阶段完成时间在近4个月内的记录（假设当前日期是2022年6月1日）。

目前写的查询因为多次调用[forecast].[DurationTable]，还要计算8次AVG，直接超时了，试了CTE也没提升性能，求能在15分钟内跑完的高效方案。

原查询示例：

SELECT [JobNumber]
,[Region]
,[Stage1]
,[Stage2]
,[Stage3]
,[Duration1]
,[Duration2]
,(
    SELECT AVG(Duration1)
    FROM [forecast].[DurationTable]
    WHERE DATEDIFF(month, Stage2, GETDATE()) <= 4
    GROUP BY Region
) AS AvgDuration1
,(
    SELECT AVG(Duration2)
    FROM [forecast].[DurationTable]
    WHERE DATEDIFF(month, Stage3, GETDATE()) <= 4
    GROUP BY Region
) AS AvgDuration2
FROM [forecast].[DurationTable]

核心思路 #

别反复扫描[forecast].[DurationTable]，先一次性算出每个Region符合条件的所有阶段平均时长，再通过JOIN把平均值关联到主数据里，全程只扫一次源表。

优化后的SQL代码 #

-- 先预计算各Region的阶段平均时长
WITH RegionAvgDurations AS (
    SELECT
        Region,
        -- 只统计Stage2在近4个月内的Duration1平均值
        AVG(CASE WHEN Stage2 >= DATEADD(month, -4, '2022-06-01') THEN Duration1 END) AS AvgDuration1,
        -- 只统计Stage3在近4个月内的Duration2平均值
        AVG(CASE WHEN Stage3 >= DATEADD(month, -4, '2022-06-01') THEN Duration2 END) AS AvgDuration2
        -- 剩下6个阶段的平均时长，照着上面的CASE逻辑扩展就行
    FROM [forecast].[DurationTable]
    GROUP BY Region
)
-- 把主表和预计算的平均值关联起来
SELECT
    dt.[JobNumber],
    dt.[Region],
    dt.[Stage1],
    dt.[Stage2],
    dt.[Stage3],
    dt.[Duration1],
    dt.[Duration2],
    rad.AvgDuration1,
    rad.AvgDuration2
    -- 其他阶段的平均字段依次加上
FROM [forecast].[DurationTable] dt
JOIN RegionAvgDurations rad ON dt.Region = rad.Region;

额外性能优化点 #

1. 调整日期筛选逻辑：原查询用DATEDIFF(month, Stage2, GETDATE()) <=4会让Stage2字段没法用索引，改成Stage2 >= DATEADD(month, -4, '2022-06-01')，如果Stage2有索引的话能直接命中，筛选速度会快很多。
2. 检查底层索引：[forecast].[DurationTable]是基于[stages].[jobStages]的视图，确认底层表的Region、各Stage日期字段有没有合适的复合索引（比如(Region, Stage2, Stage3)），能进一步缩小扫描范围。
3. 物化视图数据：如果[forecast].[DurationTable]本身计算量很大，不如把它改成定时刷新的物理表，别每次查询都实时算间隔时长。

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