如何优化生成库存异动报表的SQL查询执行速度

我有三张数据表，需要生成库存异动报表（表3），目前使用以下SQL语句实现，但该查询执行耗时过长，请问如何优化以提升执行速度？

原SQL代码：

SELECT * FROM   
(
    select  1 as 'Factor', Products_Data.ItemCost, Products_Data.Type as 'ItemType'  
        , TBL_ItemsMovement.StorageID,  Products_Data.ID 
        , Products_Data.BarCode , Products_Data.Product_Name , Products_Data.Main_Unit ,TBL_ItemsMovement.Type 
        , Products_Data.Sub_Category
        ,  (SELECT SUM((Incoming -Outgoing) * Factor)  
            FROM TBL_ItemsMovement op 
            WHERE op.ItemID = Products_Data.ID AND op.StorageID in (" + cmbstorage.EditValue.ToString() + @") 
                AND op.Insertion_Date <  CONVERT(datetime, '" + strdate.DateTime.ToString(Program.DateFormat) + @"')  
         ) as 'opening'
        ,sum((Incoming -Outgoing) * Factor) as 'Qty'  ,
        (SELECT SUM( Inventory.Qty) 
         FROM Inventory 
         WHERE Inventory.Storage_Code in (" + cmbstorage.EditValue.ToString() + @") 
             AND Inventory.ID  = Products_Data.ID
         )  as 'CurrentQty'
        , (SELECT SUM((Incoming -Outgoing) * Factor)  
           FROM TBL_ItemsMovement op 
           WHERE op.ItemID = Products_Data.ID AND op.StorageID in (" + cmbstorage.EditValue.ToString() + @") 
               AND op.Insertion_Date <  CONVERT(datetime, '" + endate.DateTime.ToString(Program.DateFormat) + @"')  
        ) as 'closed' 
    from Products_Data
    left join TBL_ItemsMovement on Products_Data.ID = TBL_ItemsMovement.ItemID 
        and TBL_ItemsMovement.StorageID in (" + cmbstorage.EditValue.ToString() + ")
        and TBL_ItemsMovement.Insertion_Date between CONVERT(datetime,  '" + strdate.DateTime.ToString(Program.DateFormat) + "'  )  and  CONVERT(datetime,  '" + endate.DateTime.ToString(Program.DateFormat) + @") 
    where  Products_Data.Type  < 4 and isdeleted = 0 
    group by Products_Data.ID ,TBL_ItemsMovement.StorageID
        ,TBL_ItemsMovement.ItemID,  Products_Data.BarCode
        , Products_Data.Product_Name,  Products_Data.Main_Unit 
        ,TBL_ItemsMovement.Type, Products_Data.Sub_Category 
        , Products_Data.Type   , TBL_ItemsMovement.StorageID
        , Products_Data.ItemCost

) t 
PIVOT (
    SUM(Qty) 
    FOR Type IN (
        [0], 
        [1], 
        [2], 
        [3], 
        [4], 
        [8], 
        [9], 
        [10],
        [11],
        [12],
        [24]
    )
) AS pivot_table;

优化方案 #

1. 替换冗余相关子查询为预聚合CTE #

原SQL中的opening、closed、CurrentQty都是相关子查询，每一行数据都会触发一次子查询执行，数据量大时会导致多次重复扫描表，严重拖慢速度。改用CTE预聚合数据，只扫描表一次：

WITH MovementAgg AS (
    -- 预计算每个商品、仓库的期初、期末、期间异动
    SELECT 
        ItemID,
        StorageID,
        Type,
        SUM(CASE WHEN Insertion_Date < @StartDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS opening,
        SUM(CASE WHEN Insertion_Date BETWEEN @StartDate AND @EndDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS Qty,
        SUM(CASE WHEN Insertion_Date < @EndDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS closed
    FROM TBL_ItemsMovement
    WHERE StorageID IN (@StorageIDs)
    GROUP BY ItemID, StorageID, Type
),
InventoryAgg AS (
    -- 预计算每个商品、仓库的当前库存
    SELECT 
        ID AS ItemID,
        Storage_Code AS StorageID,
        SUM(Qty) AS CurrentQty
    FROM Inventory
    WHERE Storage_Code IN (@StorageIDs)
    GROUP BY ID, Storage_Code
)

2. 改用参数化查询，避免动态SQL拼接 #

原代码通过字符串拼接生成SQL，不仅存在SQL注入风险，还会导致数据库无法重用执行计划。将动态参数（仓库ID、起始日期、结束日期）替换为参数化变量（如@StorageIDs、@StartDate、@EndDate），让数据库缓存执行计划，提升重复查询的效率。

3. 创建针对性索引，加速过滤与关联 #

根据查询的过滤、关联条件创建复合索引，减少表扫描次数：

• TBL_ItemsMovement：创建复合索引IX_TBL_ItemsMovement_StorageID_ItemID_InsertionDate，包含字段：StorageID, ItemID, Insertion_Date，并将Incoming, Outgoing, Factor, Type设为包含列（INCLUDE）
• Products_Data：创建索引IX_Products_Data_Type_IsDeleted，包含字段：Type, isdeleted，并将ID, BarCode, Product_Name, Main_Unit, Sub_Category, ItemCost设为包含列
• Inventory：创建复合索引IX_Inventory_StorageCode_ID，包含字段：Storage_Code, ID，并将Qty设为包含列

4. 简化分组逻辑，减少无效关联 #

原SQL的GROUP BY中存在重复字段（TBL_ItemsMovement.StorageID出现两次），且LEFT JOIN后分组会生成不必要的空行。通过预聚合CTE关联后，直接与Products_Data关联，简化分组逻辑。

优化后的完整SQL #

WITH MovementAgg AS (
    SELECT 
        ItemID,
        StorageID,
        Type,
        SUM(CASE WHEN Insertion_Date < @StartDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS opening,
        SUM(CASE WHEN Insertion_Date BETWEEN @StartDate AND @EndDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS Qty,
        SUM(CASE WHEN Insertion_Date < @EndDate THEN (Incoming - Outgoing)*Factor ELSE 0 END) AS closed
    FROM TBL_ItemsMovement
    WHERE StorageID IN (@StorageIDs)
    GROUP BY ItemID, StorageID, Type
),
InventoryAgg AS (
    SELECT 
        ID AS ItemID,
        Storage_Code AS StorageID,
        SUM(Qty) AS CurrentQty
    FROM Inventory
    WHERE Storage_Code IN (@StorageIDs)
    GROUP BY ID, Storage_Code
)
SELECT 
    1 AS Factor,
    pd.ItemCost,
    pd.Type AS ItemType,
    ma.StorageID,
    pd.ID,
    pd.BarCode,
    pd.Product_Name,
    pd.Main_Unit,
    pd.Sub_Category,
    ma.opening,
    ia.CurrentQty,
    ma.closed,
    [0], [1], [2], [3], [4], [8], [9], [10], [11], [12], [24]
FROM Products_Data pd
LEFT JOIN MovementAgg ma ON pd.ID = ma.ItemID
LEFT JOIN InventoryAgg ia ON pd.ID = ia.ItemID AND ma.StorageID = ia.StorageID
WHERE pd.Type < 4 AND pd.isdeleted = 0
PIVOT (
    SUM(Qty)
    FOR Type IN ([0], [1], [2], [3], [4], [8], [9], [10], [11], [12], [24])
) AS pivot_table;

5. 额外优化建议 #

• 避免在数据库中做日期转换：在应用层直接传递日期类型参数给SQL，不要用CONVERT(datetime, ...)转换字符串，减少数据库计算开销
• 提前过滤无效数据：确认Products_Data.Type < 4 AND isdeleted = 0是否能过滤掉大部分无效数据，减少后续关联的数据量
• 临时表优化：如果仓库范围小但数据量极大，可以将预聚合结果存入临时表，再进行关联查询，进一步提升速度

内容的提问来源于stack exchange，提问作者Amr Anwar Hassan