如何简化含子查询的重复CASE语句以提升SQL查询性能

问题背景 #

我编写的查询逻辑如下：

• 当前月份按Material关联表H获取当前值
• 2023年及之后的历史月份按Material和Mth关联历史表HHistory（H的月度备份表，含Mth字段，数据始于2023年1月）取值
• 2023年之前的所有月份，取HHistory中该Material对应字段的最早非空值

仅针对SqFt字段时查询运行正常（耗时不足1秒），但扩展至30个其他字段（如Labor、Weight等）后，运行时间急剧增加。怀疑每个字段的子查询是性能瓶颈，希望重构CASE语句或移除子查询以提升性能。

原查询代码 #

SELECT 
    I.Mth, I.Material,

    SUM(I.Units) *  CASE 
                        WHEN MONTH(I.Mth) = MONTH(GETDATE()) AND YEAR(I.Mth) = YEAR(GETDATE()) THEN H.SqFt 
                        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.SqFt 
                        ELSE (SELECT TOP 1 SqFt FROM HHistory Sub WHERE Sub.Material = I.Material AND Sub.SqFt IS NOT NULL ORDER BY Sub.KeyID) 
                    END AS [Total SqFt],
    
    SUM(I.Units) *  CASE 
                        WHEN MONTH(I.Mth) = MONTH(GETDATE()) AND YEAR(I.Mth) = YEAR(GETDATE()) THEN H.Labor
                        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.Labor
                        ELSE (SELECT TOP 1 Labor FROM HHistory Sub WHERE Sub.Material = I.Material AND Sub.Labor IS NOT NULL ORDER BY Sub.KeyID) 
                    END AS [Total Labor],

    SUM(I.Units) *  CASE 
                        WHEN MONTH(I.Mth) = MONTH(GETDATE()) AND YEAR(I.Mth) = YEAR(GETDATE()) THEN H.Weight
                        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.Weight
                        ELSE (SELECT TOP 1 Weight FROM HHistory Sub WHERE Sub.Material = I.Material AND Sub.Weight IS NOT NULL ORDER BY Sub.KeyID) 
                    END AS [Total Weight]

-- Repeat for remaining 28 fields...

FROM I
    LEFT JOIN H ON I.Material = H.Material 
    LEFT JOIN HHistory ON I.Mth = HHistory.Mth AND I.Material = HHistory.Material
GROUP BY I.Material, I.Mth, H.SqFt, HHistory.SqFt 

表结构 #

表I（生产数量） #

表H（当前物料表） #

表HHistory（H的月度备份表，部分字段可能为NULL） #

期望结果 #

优化方案 #

核心思路 #

通过CTE预计算每个Material各字段的最早非空值，避免每个字段重复执行子查询。一次性获取所有字段的默认值后再关联到主查询，大幅减少数据库的重复计算量。

优化后的查询代码 #

-- 预计算每个Material的各字段最早非空默认值
WITH MaterialDefaults AS (
    SELECT 
        Material,
        FIRST_VALUE(SqFt) OVER (PARTITION BY Material ORDER BY KeyID) AS DefaultSqFt,
        FIRST_VALUE(Labor) OVER (PARTITION BY Material ORDER BY KeyID) AS DefaultLabor,
        FIRST_VALUE(Weight) OVER (PARTITION BY Material ORDER BY KeyID) AS DefaultWeight
        -- 为剩余28个字段添加相同格式的FIRST_VALUE语句
    FROM HHistory
    WHERE 
        -- 过滤全空行，减少计算量
        SqFt IS NOT NULL OR Labor IS NOT NULL OR Weight IS NOT NULL
        -- 补充剩余字段的非空判断
)
SELECT 
    I.Mth, 
    I.Material,
    SUM(I.Units) * CASE 
        WHEN I.Mth = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) THEN H.SqFt
        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.SqFt
        ELSE md.DefaultSqFt
    END AS [Total SqFt],
    SUM(I.Units) * CASE 
        WHEN I.Mth = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) THEN H.Labor
        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.Labor
        ELSE md.DefaultLabor
    END AS [Total Labor],
    SUM(I.Units) * CASE 
        WHEN I.Mth = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) THEN H.Weight
        WHEN YEAR(I.Mth) >= 2023 THEN HHistory.Weight
        ELSE md.DefaultWeight
    END AS [Total Weight]
    -- 为剩余28个字段添加相同格式的CASE语句
FROM I
LEFT JOIN H ON I.Material = H.Material
LEFT JOIN HHistory ON I.Mth = HHistory.Mth AND I.Material = HHistory.Material
LEFT JOIN MaterialDefaults md ON I.Material = md.Material
GROUP BY 
    I.Material, 
    I.Mth, 
    -- 补充H表的所有关联字段
    H.SqFt, H.Labor, H.Weight,
    -- 补充HHistory表的所有关联字段
    HHistory.SqFt, HHistory.Labor, HHistory.Weight,
    -- 补充所有默认值字段
    md.DefaultSqFt, md.DefaultLabor, md.DefaultWeight

额外性能优化建议 #

1. 索引优化

   • 给HHistory表创建复合索引(Material, KeyID)，加速窗口函数FIRST_VALUE的计算
   • 给I表创建复合索引(Material, Mth)，提升关联和分组效率
   • 确保H表的Material字段为主键或创建唯一索引

2. 简化日期判断
   提前声明当前月变量，避免重复调用日期函数：

   DECLARE @CurrentMonth DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
   -- 后续CASE中直接使用I.Mth = @CurrentMonth
   

3. 过滤无效数据
   在CTE中严格过滤掉对默认值无贡献的全空行，减少数据集大小。

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