Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 304

WITH PAGED AS (

SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE (NOLOCK)
)

SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID

Since there is a single column identity field as a lookup on TRANS_TABLE, adding only the MYID
column will be the most efficient, but this technique can work also for multi-column primary keys. You
just need a way to join the CTE results back to the base table to retrieve the other values on the page of
the clustered index. Because you can't access the value of the ROW_NUMBER function in the CTE, you
can't reduce the realization of the rows within the CTE when you are asking for potentially everything
to be returned — imagine if @START_ROW 1,000,000. The optimizer decides it

is more efficient to return all the results just in case. You can see this in the optimized plan for the CTE
with all columns with a SEEK operation against each row.

= 1and@MAX_ROWS =

Rows Executes Statement Text

1000000 1000000 |--Clustered Index Seek(OBJECT:([PK_TRANS_TABLE]),

SEEK:([MYID]=[MYID]) LOOKUP ORDERED FORWARD)

After moving the T-SQL around, this SEEK activity drops to match our intentions:

Rows Executes Statement Text

25 25 |--Clustered Index Seek(OBJECT:([PK__TRANS_TABLE] AS [TT]),

SEEK:([TT].[MYID]=[MYID]) ORDERED FORWARD)

Not only are the number of rows reduced, but you'll also notice that the seek operation is no longer a
bookmark lookup operation. You learned about reducing these operators earlier in this chapter, and now
you can see again the savings with this T-SQL rewrite. Table 9-27 shows costs for retrieving first and last
pages in.

Table 9-27: CTE Paging Performance Metrics

Method % Cost CPU Reads Duration

First Page

ALL COLS IN CTE 87 0 105 138ms

USE JOIN FOR COLS 13 10 78 95ms

Last Page

ALL COLS IN CTE 87 6580 3189758 6942ms

USE JOIN FOR COLS 13 1031 2312 1179ms