Home Forums SQL Server 2008 SQL Server Newbies Performance issue with subquery containing row_number and partitioning RE: Performance issue with subquery containing row_number and partitioning

  • 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]