SQL新手求助：慢查询优化，现有SQL执行耗时2-3分钟

你的核心问题是多表关联后产生大量重复数据，被迫用sum(distinct)修正，但sum(distinct)是性能杀手——它需要对数据去重后再聚合，耗时极高。下面是针对性的优化方案：

优化思路 #

先对每个表单独做预聚合（按分组维度+时间维度），再关联预聚合后的结果，从根源避免数据膨胀，彻底去掉sum(distinct)。

优化后的SQL #

WITH StockAgg AS (
    -- 预聚合库存表数据，按物料、工厂、月末日期分组
    SELECT
        Material,
        Plnt,
        EomonthMB5b,
        SUM([opening stock]) AS opening_stock,
        SUM([closing stock]) AS closing_stock
    FROM tblStockmb5b
    GROUP BY Material, Plnt, EomonthMB5b
),
ConsumptionAgg AS (
    -- 预聚合消耗表数据，按物料、工厂、月末日期分组
    SELECT
        Material,
        Plant,
        Eomonth261,
        SUM(Quantity) AS TotalQuantity
    FROM tblConsumtion261
    GROUP BY Material, Plant, Eomonth261
),
-- 提前计算日期差，避免重复计算
DateDiffCalc AS (
    SELECT DATEDIFF(day, '2020-01-01', GETDATE()) AS DaysSince2020
)
SELECT
    s.Material,
    s.Plnt,
    SUM(s.opening_stock) AS [opening stockM],
    SUM(s.closing_stock) AS [closing stockM],
    SUM(cp.unitvalue * s.opening_stock) AS TotalOpeningStockCOGS,
    SUM(cp.unitvalue * s.closing_stock) AS TotalClosingStockCOGS,
    (SUM(cp.unitvalue * s.opening_stock) + SUM(cp.unitvalue * s.closing_stock)) / 2 AS AverageStockPerMonth,
    ISNULL(SUM(c.TotalQuantity), 0) AS TotalMonthDemand,
    ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) AS TotalannualDemand,
    ((SUM(cp.unitvalue * s.opening_stock) + SUM(cp.unitvalue * s.closing_stock)) / 2) / COUNT(DISTINCT s.EomonthMB5b) AS AverageInventoryValue,
    CASE 
        WHEN ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) = 0 THEN NULL
        ELSE ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) / dc.DaysSince2020 
    END AS AverageDailyCOGS,
    CASE 
        WHEN ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) = 0 THEN NULL
        WHEN ((SUM(cp.unitvalue * s.opening_stock) + SUM(cp.unitvalue * s.closing_stock)) / 2) = 0 THEN NULL
        ELSE ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) / 
             (((SUM(cp.unitvalue * s.opening_stock) + SUM(cp.unitvalue * s.closing_stock)) / 2) / COUNT(DISTINCT s.EomonthMB5b))
    END AS [Inv.Turnover Ratio],
    CASE 
        WHEN ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) = 0 THEN NULL
        ELSE (((SUM(cp.unitvalue * s.opening_stock) + SUM(cp.unitvalue * s.closing_stock)) / 2) / COUNT(DISTINCT s.EomonthMB5b)) / 
             (ISNULL(SUM(c.TotalQuantity * cp.unitvalue), 0) / dc.DaysSince2020)
    END AS IDS
FROM StockAgg s
LEFT JOIN tblCOGSPrice cp
    ON s.Material = cp.Material 
    AND s.Plnt = cp.Plant 
    AND s.EomonthMB5b = cp.EomonthC9
LEFT JOIN ConsumptionAgg c
    ON s.Material = c.Material 
    AND s.Plnt = c.Plant 
    AND s.EomonthMB5b = c.Eomonth261
CROSS JOIN DateDiffCalc dc
GROUP BY s.Material, s.Plnt, dc.DaysSince2020
ORDER BY s.Material, s.Plnt

关键优化点说明 #

1. 预聚合消除数据膨胀：
   先对tblStockmb5b和tblConsumtion261按Material+Plnt+时间维度聚合，确保每个维度组合只有一条数据，关联时不会产生笛卡尔积，彻底不需要sum(distinct)。
2. 避免重复计算：
   用CTEDateDiffCalc提前计算一次日期差，避免在多个CASE语句中重复执行DATEDIFF。
3. 替换IIF为CASE：
   CASE在SQL Server中兼容性更好，写法更清晰，执行效率与IIF相当。
4. 用ISNULL处理空值：
   避免聚合结果为NULL导致后续计算异常，逻辑更明确。

额外索引建议 #

为预聚合的分组字段创建覆盖索引，进一步提升聚合速度：

• 对tblStockmb5b创建：CREATE NONCLUSTERED INDEX IX_tblStockmb5b_MaterialPlntEOM ON tblStockmb5b (Material, Plnt, EomonthMB5b) INCLUDE ([opening stock], [closing stock]);
• 对tblConsumtion261创建：CREATE NONCLUSTERED INDEX IX_tblConsumtion261_MaterialPlantEOM ON tblConsumtion261 (Material, Plant, Eomonth261) INCLUDE (Quantity);
• 对tblCOGSPrice创建：CREATE NONCLUSTERED INDEX IX_tblCOGSPrice_MaterialPlantEOM ON tblCOGSPrice (Material, Plant, EomonthC9) INCLUDE (unitvalue);

这些索引可以让数据库直接从索引中获取聚合所需的所有数据，不需要回表查询。

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