1

I want to find classical Greatest N per Group. I have two methods to solve this problem

  1. Dense_rank Over() method
  2. Min Over() method

Both work flawlessly. Now I want to find which one is better and why.

Sample data:

CREATE TABLE #test
    (
        id    INT,
        NAME  VARCHAR(50),
        dates DATETIME
    );

WITH cte
    AS (SELECT TOP (100000) n = ( (Row_number() OVER (ORDER BY a.number) - 1) / 3) + 1
        FROM   [master]..spt_values a
            CROSS JOIN [master]..spt_values b)
INSERT INTO #test
SELECT n,
        Getdate() + n
FROM   cte ;

Dense_rank Over() Method:

WITH cte
     AS (SELECT Dense_rank() OVER (partition BY NAME ORDER BY dates) AS rn,*
         FROM   #test)
SELECT id,
       NAME,
       dates
FROM   cte
WHERE  rn = 1;

Min Over() Method:

WITH cte
     AS (SELECT Min(dates) OVER (partition BY NAME) AS max_date,*
         FROM   #test)
SELECT id,
       NAME,
       dates
FROM   cte
WHERE  max_date = dates ;

To compare the performance I checked on execution plan which said both query cost is 50%. But Max Over method execution plan looks little complex. So any guidance here will be helpful. I don't have very good understanding in execution plans.

Execution Plan:(6 rows)

enter image description here

Execution plan:(100000 rows)

For 100000 rows the execution plan says

Dense_rank Over() Query cost : 46% 
Min Over() Query cost        : 54%

enter image description here

  • 5
    You should make a more realistic test, e.g. do you really not plan on having any indexes on your production tables, never mind an index that might help support this query? – Aaron Bertrand Feb 29 '16 at 4:35
  • Not to mention that the percents in the plans may not accurately represent the differences in performance. You should time your queries and compare the actual execution times. – Andriy M Feb 29 '16 at 8:43