Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alternative to using ROW_NUMBER for better performance

Tags:

sql

sql-server

I have a small query below where it outputs a row number under the RowNumber column based on partitioning the 'LegKey' column and ordering by UpdateID desc. This is so the latest updated row (UpdateID) per legkey is always number 1

SELECT *
, ROW_NUMBER() OVER(PARTITION BY LegKey ORDER BY UpdateID DESC) AS RowNumber 
FROM Data.Crew

Data outputted:

UpdateID    LegKey  OriginalSourceTableID   UpdateReceived          RowNumber
7359        6641    11                     2016-08-22 16:35:27.487  1
7121        6641    11                     2016-08-15 00:00:47.220  2
8175        6642    11                     2016-08-22 16:35:27.487  1
7122        6642    11                     2016-08-15 00:00:47.220  2
8613        6643    11                     2016-08-22 16:35:27.487  1
7123        6643    11                     2016-08-15 00:00:47.220  2

The problem I have with this method is that I am getting slow performance because I assume I am using the ORDER BY.

My question is that is there an alternative way to produce a similar result but have my query run faster? I am thinking a MAX() may work but I didn't get the same output as before. Maybe I did the MAX() statement incorrectly so was wondering if this is a good alternative if somebody can provide an example on how they would write the MAX() statement for this example?

Thank you

like image 871
BruceyBandit Avatar asked Sep 02 '25 15:09

BruceyBandit


1 Answers

Presumably this is the query you want to optimize:

SELECT c.*
FROM (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY LegKey ORDER BY UpdateID DESC) AS RowNumber 
      FROM Data.Crew c
     ) c
WHERE RowNumber = 1;

Try an index on Crew(LegKey, UpdateId).

This index will also be used if you do:

SELECT c.*
FROM Data.Crew c
WHERE c.UpdateId = (SELECT MAX(c2.UpdateId)
                    FROM Data.Crew c2
                    WHERE c2.LegKey = c.LegKey
                   );
like image 89
Gordon Linoff Avatar answered Sep 04 '25 13:09

Gordon Linoff