Wednesday, August 22, 2007

How to select a second page faster

I used to think that using TOP clause to select a second page is faster than using ROW_NUMBER(). Apparently this is no longer true (although the plans are somewhat different):

-- this populates IDs with 3M rows, and the CAST strips IDENTITY property
SELECT CAST(my_ID*1 AS BIGINT) as ID INTO IDs FROM MyTable
GO
CREATE UNIQUE CLUSTERED INDEX PK_IDs ON IDs(ID)
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT TOP 20 ID FROM(
SELECT TOP 40 ID FROM IDs ORDER BY ID) t
ORDER BY ID DESC
GO
/*
Table 'IDs'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

A simple execution plan. Correct cardinality estimate:
Estimated Number of Rows: 40
*/
SELECT ID FROM(
SELECT ID, ROW_NUMBER() OVER(ORDER BY ID) rn FROM IDs) t
WHERE rn BETWEEN 21 AND 40
GO
/*
Same real execution costs:
Table 'IDs'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

A different, more complex plan.
I was surprized to see cost of one step exceed 100%: Segment Cost: 1771%
Also this estimate was somewhat askew: Estimated Number of Rows: 100
*/

0 Comments:

Post a Comment

<< Home