Efficiently select top row for each category in the set

Question

I need to select a top row for each category from a known set (somewhat similar to this question). The problem is, how to make this query efficient on the large number of rows.

For example, let's create a table that stores temperature recording in several places.

CREATE TABLE #t (
    placeId int,
    ts datetime,
    temp int,
    PRIMARY KEY (ts, placeId)
)

-- insert some sample data

SET NOCOUNT ON

DECLARE @n int, @ts datetime
SELECT @n = 1000, @ts = '2000-01-01'

WHILE (@n>0) BEGIN
    INSERT INTO #t VALUES (@n % 10, @ts, @n % 37)
    IF (@n % 10 = 0) SET @ts = DATEADD(hour, 1, @ts)
    SET @n = @n - 1
END

Now I need to get the latest recording for each of the places 1, 2, 3.

This way is efficient, but doesn't scale well (and looks dirty).

SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 1
    ORDER BY ts DESC
) t1
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 2
    ORDER BY ts DESC
) t2
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 3
    ORDER BY ts DESC
) t3

The following looks better but works much less efficiently (30% vs 70% according to the optimizer).

SELECT placeId, ts, temp FROM (
    SELECT placeId, ts, temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum
    FROM #t
    WHERE placeId IN (1, 2, 3)
) t
WHERE rownum = 1

The problem is, during the latter query execution plan a clustered index scan is performed on #t and 300 rows are retrieved, sorted, numbered, and then filtered, leaving only 3 rows. For the former query three times one row is fetched.

Is there a way to perform the query efficiently without lots of unions?

Answer 1

don't just look at the execution plan also look at statistics io and statistics time

set statistics io on
go
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 1
    ORDER BY ts DESC
) t1
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 2
    ORDER BY ts DESC
) t2
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 3
    ORDER BY ts DESC
) t3

SELECT placeId,  temp FROM (
    SELECT placeId, ts, temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum
    FROM #t
    WHERE placeId IN (1, 2, 3)
) t
WHERE rownum = 1

set statistics io off
go

Table '#t000000000B99'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#t000000000B99'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

set statistics time on
go
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 1
    ORDER BY ts DESC
) t1
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 2
    ORDER BY ts DESC
) t2
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 3
    ORDER BY ts DESC
) t3

SELECT placeId,  temp FROM (
    SELECT placeId, ts, temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum
    FROM #t
    WHERE placeId IN (1, 2, 3)
) t
WHERE rownum = 1

set statistics time on
go

To me there is no real difference between the 2 methods, load up more data and compare again

ALso when you add an order by to both queries it drops to 40% vs 60%

SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 1
    ORDER BY ts DESC
) t1
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 2
    ORDER BY ts DESC
) t2
UNION ALL
SELECT * FROM (
    SELECT TOP 1 placeId, temp
    FROM #t 
    WHERE placeId = 3
    ORDER BY ts DESC
) t3
ORDER BY placeId

SELECT placeId,  temp FROM (
    SELECT placeId,  temp, ROW_NUMBER() OVER (PARTITION BY placeId ORDER BY ts DESC) rownum
    FROM #t
    WHERE placeId IN (1, 2, 3)
) t
WHERE rownum = 1
ORDER BY placeId

Answer 2

I loaded 100,000 rows (which still wasn't enough to slow things down), tried the old-fashioned way:

select t.*
 from #t t
  inner join (select placeId, max(ts) ts
               from #t
               where placeId in (1,2,3)
               group by placeId) xx
   on xx.placeId = t.placeId
    and xx.ts = t.ts

and got much the same results.

I then reversed the order of the columns in the index, to

CREATE TABLE #t ( 
    placeId int, 
    ts datetime, 
    temp int, 
    PRIMARY KEY (placeId, ts) 
) 

and, in all the queries, got fewer page reads and index seeks instead of scans.

If optimization is your goal and you can modify indexes, I'd revised the primary key, or perhaps add a covering index.

Answer 3

Just for the record, another option using CROSS APPLY.
On my configuration, it performs better than all previously mentioned ones.

SELECT *
FROM (VALUES (1),(2),(3)) t (placeId)
CROSS APPLY (
    SELECT TOP 1 ts, temp
    FROM #t 
    WHERE placeId = t.placeId
    ORDER BY ts DESC
) tt

I'd guess, VALUES could be chaged to a temp table or a table variable without much difference.