SQL Server Filter query results to only those in which the Parent Column's ItemID is not present in the Child column in T-SQL

pftdvrlh 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(107)

So I have a bill of materials table that shows one column (ManufacturedItemNumber) containing the ItemID of the item produced (which I am calling the parent column, and is enumerated for each sub-item on the BOM), the next column (ItemNumber) displays the ItemID for all of the sub-items that are used in the creation of that parent item as rows.

Children of a BOM can have children of their own. I am trying to find the highest-level items, items which do not get used in the production of other items.

My query:

This is the filter that does what I want:

select  1 as ParentItemFLag,
        bomparent.ManufacturedItemNumber
from    BomLines bomparent
where   not exists (select scr1.* from BomLines scr1 where scr1.ItemNumber = bomparent.ManufacturedItemNumber) and bomparent.ManufacturedItemNumber is not null

This the problem area, I want to filter the larger table's results to only those I was able to filter, but the performs is really bad:

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

This is the problem spot in the execution plan: Execution Plan

I am expecting to filter the entire table by my subquery via an inner join, but for some reason the query is having trouble matching the subquery results to the main query's table.

sql-server

来源：https://stackoverflow.com/questions/75836096/filter-query-results-to-only-those-in-which-the-parent-columns-itemid-is-not-pr

1条答案

按热度按时间

j8ag8udp1#

What about using a CTE instead of a subquery? I think the subquery you're using now is causing that high cost on the right outer join because it's executing for each row in the outer query. For the CTE it's only executed once then used in the main query potentially providing better performance

WITH TopLevelItems AS (
    SELECT 1 AS ParentItemFLag,
           ManufacturedItemNumber
    FROM BomLines
    WHERE NOT EXISTS (
        SELECT 1 FROM BomLines WHERE ItemNumber = BomLines.ManufacturedItemNumber
    ) AND ManufacturedItemNumber IS NOT NULL
)
, CTE AS (
    SELECT BomID, b.ManufacturedItemNumber, ManufacturedItemGroupID, Quantity, t.ParentItemFLag
    FROM BomLines b
    INNER JOIN TopLevelItems t ON t.ManufacturedItemNumber = b.ManufacturedItemNumber
)
SELECT ParentItemFLag, BomID, ManufacturedItemNumber, ManufacturedItemGroupID, Quantity
FROM CTE;

fiddle

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Filter query results to only those in which the Parent Column's ItemID is not present in the Child column in T-SQL

1条答案

相关问题

热门标签

最新问答