T-SQL过滤BOM表顶级物料：关联子查询性能优化求助

核心问题分析 #

查询性能差的主要原因是子查询重复计算，加上缺少合适索引导致关联阶段出现全表扫描，原内连接写法会先生成所有顶级物料的中间结果，再与主表做关联，数据量大时匹配开销会急剧上升。

优化后的查询写法 #

写法1：直接用NOT EXISTS关联主表 #

无需单独生成中间子查询，直接在主表查询中嵌入NOT EXISTS条件，数据库优化器能生成更高效的执行计划：

select  
    1 as ParentItemFLag,
    bom1.BomID, 
    bom1.ManufacturedItemNumber, 
    bom1.ManufacturedItemGroupID, 
    bom1.Quantity
from bomlines bom1
where 
    bom1.ManufacturedItemNumber is not null
    and not exists (
        select 1 from BomLines scr1 
        where scr1.ItemNumber = bom1.ManufacturedItemNumber
    )

注意把select scr1.*改为select 1——NOT EXISTS只需要判断存在性，不需要返回全列，能减少不必要的资源消耗。

写法2：LEFT JOIN + IS NULL替代NOT EXISTS #

部分数据库对LEFT JOIN的优化逻辑更友好，可尝试替换写法：

select  
    1 as ParentItemFLag,
    bom1.BomID, 
    bom1.ManufacturedItemNumber, 
    bom1.ManufacturedItemGroupID, 
    bom1.Quantity
from bomlines bom1
left join BomLines scr1 
    on scr1.ItemNumber = bom1.ManufacturedItemNumber
where 
    bom1.ManufacturedItemNumber is not null
    and scr1.ItemNumber is null

关键索引优化 #

必须添加以下索引来加速关联和过滤：

• 给ItemNumber列创建非聚集索引：

CREATE INDEX IX_BomLines_ItemNumber ON BomLines(ItemNumber)

• 给ManufacturedItemNumber列创建覆盖索引，包含查询所需返回列，避免回表操作：

CREATE INDEX IX_BomLines_ManufacturedItemNumber_Covering 
ON BomLines(ManufacturedItemNumber)
INCLUDE (BomID, ManufacturedItemGroupID, Quantity)

覆盖索引让数据库直接从索引中获取全部所需数据，无需访问主表数据页，能大幅提升查询速度。

额外优化建议 #

• 子查询中永远避免用select *，NOT EXISTS场景下用select 1或select null即可满足判断需求。
• 如果ManufacturedItemNumber存在大量重复值，可在原内连接的子查询中加入DISTINCT，缩小中间结果集规模：

select  scr2.ParentItemFLag, bom1.BomID, bom1.ManufacturedItemNumber, bom1.ManufacturedItemGroupID, bom1.Quantity
from    bomlines bom1
inner join  (
                select distinct
                    1 as ParentItemFLag,
                    bomparent.ManufacturedItemNumber
                from    BomLines bomparent
                where   not exists (select 1 from BomLines scr1 where scr1.ItemNumber = bomparent.ManufacturedItemNumber) 
                        and bomparent.ManufacturedItemNumber is not null
            ) scr2 on scr2.ManufacturedItemNumber = bom1.ManufacturedItemNumber

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