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

-- Get the first row for the page
SELECT TOP(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC
FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDESC, MYID
SELECT TOP(@MAX_ROWS) *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID > = @START_ROW
AND MYDESC > = @START_DESC
ORDER BY MYDESC, MYID
TempTablePaging
There are a lot of Temp table paging procedures in use, and until recently, it was not clear to us why
they were being used. The performance issue is that even when used to lay down a copy of the
clustered index key and the results of a ranking function, you can't get away from having to write
the same results that you'll later have to reread. This is not a huge impact when the resultsets are
small, but as the result row count grows this becomes the wrong method to use. The temptation to
use a temp table is understandable — especially if the UI requires a return of the total number of fully
realized predicate rows. If you noticed, the other method examples in this section never fully realized
the predicate. Either the optimizer took a shortcut or the ROW_COUNT/TOP operations resulted in
only partially realizing all the rows of the predicate. This is the secret as to why these optimizations
work so well. However, if you have to return the total row count of the predicate, then pulling all
the clustered IDs that match the predicate into a temp table allows you to use @@ROWCOUNT to
instantly interrogate the number of a rows added, and then still have the table around to use in
aJOIN.
This sounds good, and works well on small tables. However, using the one-million-row TRANS_DATA
table, even a simple paging operation will eat up some I/O in tempdb. Here's a sample of the temp table
paging using TRANS_DATA.
DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;
SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,
MYID
into #TEMP
FROM TRANS_TABLE (NOLOCK)
SELECT TT.*
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN #TEMP T
ON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
DROP TABLE #TEMP
Again, notice that, like the CTE example, only the most important fields are in the ranking number
function, that is written to the temp table #TEMP. The remainder is pulled in from the base table. This is
at least a better option than dumping all column and row contents into the temp table. Table 9-30 shows
the results of comparing to the CTE example.