Performance issue with subquery containing row_number and partitioning

Question

Post reply

Performance issue with subquery containing row_number and partitioning

tarr94

SSCommitted

Points: 1684
More actions
October 23, 2014 at 11:30 am

#294419

Hi,
I'm developing a query that does what I want it to, but takes a long time to run. I thought I'd see if the smart folks on this site had any suggestions for an alternative approach.
My query looks something like this:
SELECT Equipment.EquipID, subInvoice.EquipmentAmount
FROM Equipment
LEFT JOIN (
SELECT InvoiceEquipment.EquipID,
InvoiceEquipment.Amount AS 'EquipmentAmount',
ROW_NUMBER() OVER(PARTITION BY InvoiceEquipment.EquipID ORDER BY InvoiceEquipment.InvoiceDate DESC) AS 'RowNumber'
FROM InvoiceEquipment
WHERE Invoice.Type = 'A'
) subInvoice ON subInvoice.EquipID = Equipment.EquipID AND subInvoice.RowNumber = 1
Basically, each equipment can appear on multiple invoices, and the amount for the equipment can vary for each invoice. I want to select the most recent EquipmentAmount for each EquipID.
The InvoiceEquipment table is quite large, so I'm hoping someone knows of a better solution.
Any help is greatly appreciated.
Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sqlslacker SSChasing Mays Points: 657 More actions · Answer 1

I'd probably change my approach up a little bit.

SELECT Eq.EquipID, IE.Amount as EquipAmount

FROM Equipment Eq

INNER JOIN InvoiceEquipment IE

ON Eq.EquipID = IE.EquipID

WHERE IE.Type = 'A'

AND NOT EXISTS (

SELECT 1

FROM InvoiceEquipment

ON IE.EquipID = EquipID

AND InvoiceDate > IE.InvoiceDate

)

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'd rather use the APPLY operator:

SELECT Equipment.EquipID, x.EquipmentAmount

FROM Equipment

OUTER APPLY

(

SELECT TOP 1 InvoiceEquipment.Amount AS 'EquipmentAmount'

FROM InvoiceEquipment

WHERE InvoiceEquipment.Type = 'A' AND InvoiceEquipment.EquipID = Equipment.EquipID

ORDER BY InvoiceEquipment.InvoiceDate DESC

)x

From my point of view the code is easier to read. And for sure it'll perform better since ther's only one row returned and no need to number all rows just to use the first one...

Edit: An index on the InvoiceEquipment table with EquipID, InvoiceDate and Type together with EquipmentAmount as included column will help, too.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tarr94 SSCommitted Points: 1684 More actions · Answer 3

sqlslacker and LutzM,

Thank you both! I will give these a try.

Daniel Matthee Mr or Mrs. 500 Points: 579 More actions · Answer 4

Hi

Try a cover index

something in the line of

CREATE NONCLUSTERED INDEX Idx_CoverIndex ON InvoiceEquipment (EquipID,InvoiceDate DESC) inlcude (Amount,Type)

Kind Regards

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za