SQL Server：优化FIFO库存现有量最新成本版本查询性能

我明白你现在的痛点：用TOP1相关子查询虽然能得到正确的FIFO计价结果，但全公司库存列表查询耗时超16分钟，这在大数据量下确实没法接受。咱们来拆解下问题，然后给出几个能显著提升性能的优化方案。

先说说原查询的性能瓶颈 #

你当前的LEFT JOIN搭配相关子查询的写法，会导致数据库对inventoryonHand的每一行都执行一次子查询去standardcosts里找符合条件的记录——相当于做了N次小查询（N是库存记录数），数据量一大，性能自然雪崩。

方案1：用OUTER APPLY替代相关子查询（改动最小，效果明显） #

SQL Server里的OUTER APPLY比传统的相关子查询执行效率更高，它的执行计划会更智能，能减少重复查询的次数。写法和原逻辑完全一致，只是换了关联方式：

SELECT 
    ioh.*,
    sc.costamt,
    sc.effdate
FROM inventoryonHand ioh
OUTER APPLY (
    SELECT TOP 1 sc2.effdate, sc2.costamt
    FROM standardcosts sc2
    WHERE sc2.partID = ioh.partID
      AND sc2.effdate < ioh.transDate
    ORDER BY sc2.effdate DESC
) sc;

方案2：添加针对性索引（核心优化点） #

不管用哪种写法，合适的索引都是提升性能的关键。给这两张表创建以下复合索引：

1. 给standardcosts表创建索引，让数据库能快速定位每个物料的最新有效成本：

CREATE NONCLUSTERED INDEX IX_StandardCosts_PartID_EffDate 
ON standardcosts (partID, effdate DESC) 
INCLUDE (costamt);

这个索引把partID作为第一键，effdate倒序排列，同时包含costamt字段——这样数据库不需要回表就能拿到需要的所有数据，直接在索引里完成筛选和排序。

2. 给inventoryonHand表创建辅助索引，优化关联时的匹配效率：

CREATE NONCLUSTERED INDEX IX_InventoryOnHand_PartID_TransDate 
ON inventoryonHand (partID, transDate);

方案3：预生成成本区间（适合成本更新不频繁的场景） #

如果你的物料成本不是天天更新，那可以把成本记录转换成生效区间，然后用区间匹配替代逐行查找，这能把关联复杂度从O(N*M)降到O(N+M)，性能提升非常显著。

先通过CTE生成每个成本记录的生效区间（下一个成本生效日期就是当前成本的结束日期）：

WITH CostIntervals AS (
    SELECT 
        partID,
        effdate AS start_date,
        -- 用LEAD函数获取下一个成本的生效日期，没有的话用当前日期作为结束
        LEAD(effdate, 1, GETDATE()) OVER (PARTITION BY partID ORDER BY effdate) AS end_date,
        costamt
    FROM standardcosts
)
SELECT 
    ioh.*,
    ci.costamt,
    ci.start_date AS effdate
FROM inventoryonHand ioh
LEFT JOIN CostIntervals ci 
    ON ci.partID = ioh.partID
    AND ioh.transDate >= ci.start_date
    AND ioh.transDate < ci.end_date;

这样库存交易日期只要落在某个成本区间内，就能直接匹配到对应的成本，一次关联就能完成所有匹配，速度会快很多。

优先级建议 #

1. 先加方案2的索引，这是最立竿见影的优化，不需要改太多代码；
2. 然后把原查询改成方案1的OUTER APPLY写法，配合索引能把耗时降到几分钟甚至更短；
3. 如果成本更新频率很低，方案3是最优选择，能把性能拉到极致。

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