Problem optimizing sql query with cross apply sub query

user_1596734 0
Viewed 162

So I have three tables:

MakerParts, that holds the primary information of a Vehicle Part:

Id MakerId PartNumber Description
1 1 ABC1234 Tire
2 1 XYZ1234 Door

MakerPrices, that holds the price history variation for the parts (references MakerParts.Id on MakerPartNumberId, and the table MakerPriceUpdates on UpdateId):

Id MakerPartNumberId UpdateId Price
1 1 1 9.83
2 1 2 11.23

MakerPriceUpdates, that holds the date of prices updates. This update is basically a CSV file that is uploaded to our system. One file, one line on this table, multiple prices changes on the table MakerPrices.

Id Date FileName
1 2019-01-09 00:00:00.000 temp.csv
2 2019-01-11 00:00:00.000 temp2.csv

This means that one part (MakerParts) may have multiple prices (MakerPrices). The date of the price change is on the table MakerPricesUpdates.

I want to select all MakerParts where the most recent price is zero, filtering by the MakerId on table MakerParts.

What I've tried:

select mp.* from MakerParts mp cross apply
    (select top 1 Price from MakerPrices inner join 
        MakerPricesUpdates on MakerPricesUpdates.Id = MakerPrices.UpdateId where 
        MakerPrices.MakerPartNumberId = mp.Id order by Date desc) as p
where mp.MakerId = 1 and p.Price = 0

But that is absurdly slow (we have about 100 million lines on the MakerPrices table). I'm having a hard time optimizing this query. (the result is only two rows for the MakerId 1, and it took 2 mins to run). I also tried:

select * from (
    select 
        mp.*,
        (select top 1 Price from MakerPrices inner join 
        MakerPricesUpdates on MakerPricesUpdates.Id = MakerPrices.UpdateId 
        where MakerPrices.MakerPartNumberId = mp.Id order by Date desc) as Price
    from MakerParts mp) as temp
where temp.Price = 0 and MakerId = 1

Same result, and same time. My query plan (for the first query) (no new indexes suggested by Management Studio):

enter image description here

query-optimization sql performance
3 Answers

I think you can avoid joining MakerPriceUpdates with makerprices since with the highest UpdateId you can find the latest price updates. It will save you some time.

select mp.* from MakerParts mp cross apply
    (select top 1 Price from MakerPrices where 
        MakerPrices.MakerPartNumberId = mp.Id order by MakerPrices.UpdateId desc) as p
where mp.MakerId = 1 and p.Price = 0

You can further reduced some times by avoiding sort and order by with cte and row_number() as below:

;with LatestMakerPrices as
(
    select  *,row_number()over(partition by MakerPartNumberId order by updateid desc)rn from MakerPrices 
)
select mp.* from MakerParts mp cross apply
    (select price from LatestMakerPrices lmp where lmp.MakerPartNumberId=mp.Id) as p
where mp.MakerId = 1 and p.Price = 0

Execution plan difference between query in question and my answer: enter image description here

user_86516010

try:

WITH tab AS (
    SELECT *, NULL as Price FROM MakerParts
    WHERE not exists ( 
        SELECT Id
        FROM MakerPrices
        WHERE MakerPrices.MakerPartNumberId = MakerParts.Id 
    )  
)

SELECT * from tab WHERE MakerId = 2
UNION ALL
SELECT a.* , Price
FROM [dbo].[MakerParts] a
    LEFT JOIN [dbo].[MakerPrices] b
    ON b.MakerPartNumberId = a.Id
WHERE MakerId = 2 AND Price = 0
user_98618640

Try your query:

select mp.* from MakerParts mp cross apply
    (select top 1 Price from MakerPrices inner join 
        MakerPricesUpdates on MakerPricesUpdates.Id = MakerPrices.UpdateId where 
        MakerPrices.MakerPartNumberId = mp.Id order by Date desc) as p
where mp.MakerId = 1 and p.Price = 0

After creating below index:

CREATE NONCLUSTERED INDEX [NCIdx_MakerPrices_MakerPartNumberId_UpdateId] ON [dbo].[MakerPrices]
(
    [MakerPartNumberId] ASC,
    [UpdateId] ASC
)
INCLUDE([Price])

And making ID column of MakerPricesUpdates table primary key.

user_86516010
Related