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

Table 9-30: CTE Sort Paging Metrics

Method % Cost CPU Reads Duration

First Page

TEMP 99 7300 19208* 9799ms

CTE 1 30 82 121ms

Last Page

19197 ∗ TEMP 99 7090 12903ms

CTE 1 781 2237 899ms

*not including writes

When you look at the comparison results, it is clear that this solution really beats up on your
tempdb database and the response time is unacceptable in today's terms for a one-million row resultset.
However, if the WHERE clause produces consistent multi-hundred row resultsets, the impact is not quite
so pronounced and you may be able to use this method for these use cases.

ReturningTotalPageNumbers
One of the advantages of using the TEMP table paging method is that returning the total realized rows
is easy and cheap, but does this outweigh the cost of the I/O? Capture the total realized rows from the
predicate using @@ROWCOUNT after inserting the rows into the temp table like this:

SET @TOT_ROW_CNT = @@ROWCOUNT

Using ROWCOUNT is almost free. All other methods have to run a separate count operation, since

the resultsets in the examples aren't fully realized. The operation to retrieve total row counts for page
calculations requires a re-querying of the base table, including the WHERE predicate:

➪

SELECT @TOT_ROW_CNT = COUNT(*) FROM TRANS_TABLE (NOLOCK)
--++WHERE CONDITION

This is not a cheap operation. You should use the * in the COUNT(*) operation to allow the optimizer to
use the best index to perform the count on, but every leaf on an index will be read to perform this count.
You can see the work performed by looking at the plan.

Rows Statement Text
1000000 |--Index Scan(OBJECT:([IX_TRANS_TABLE_MYGROUPID]))

For the TRANS_TABLE all 100000 rows in the index are read, resulting in about 1740 page reads on the
smallest index IX_TRANS_TABLE_MYGROUPID. If the clustered index was the only index that can be
read, this would be much more expensive. The expense of this summation may even lead you to believe
that using the temp table method is cheaper for paging and sorting.

Table 9-31 shows the comparison of the two methods.