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

you see that this can't be the case. The number of I/O pages accessed by the MAX query is 33 percent more
than the TOP query. If you look at the deltas in the subtree costs, you'll see the reason for the disparity
is because the optimizer is overestimating the amount of sort I/O involved in executing the ORDER BY
statement. The MAX query doesn't have to perform a sort to get the max transactionId, but the additional
cost is borne by the second seek into the clustered index. This increases the I/O activity and the overall
cost and duration. This is a case where you have to look further into the actual results to determine which
method is best. It is clear so far that the TOP query is working best.
However, using the knowledge of the highly selective index, a rewrite of the MAX query can provide
very detailed information to the optimizer:
SELECT *
FROM production.transactionhistoryarchive
WHERE transactionid = (
SELECT max(transactionId)
FROM production.transactionhistoryarchive
WHERE referenceOrderId = 5633
)
AND ProductId = 399
AND referenceOrderId = 5633
AND transactiondate = '2001-11-18 00:00:00.000'
AND quantity > 2
AND actualcost > =0
The rewrite change seems insignificant on the surface, but if you examine the information in the
SHOWPLAN_ALL option below, you can see that the second method is able to use the clustered index
and the non-clustered index to reduce the I/O. The results of this second MAX query and a comparison
against the other methods is provided in Table 9-23. Note that the number of I/O pages in the second
MAX query has been reduced. Also note that the optimizer is wrong when estimating the results of
pure costs.
SHOWPLAN_ALL INFO FOR MAX1-
|--Index Seek(OBJECT:([IX_ReferenceOrderLineID]),
SEEK:([ReferenceOrderID]=(5633)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PK_TransactionHistoryArchive_TransactionID]),
SEEK:([TransactionID]=[TransactionID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PK_TransactionHistoryArchive_TransactionID]),
SEEK:([TransactionID]=[Expr1006]) ORDERED FORWARD)
SHOWPLAN_ALL INFO FOR MAX2-
|--Index Seek(OBJECT:([IX_ReferenceOrderLineID]),
SEEK:([ReferenceOrderID]=(5633)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PK_TransactionHistoryArchive_TransactionID]),
SEEK:([TransactionID]=[Expr1006]),
WHERE:([ProductID]=(399) AND [ReferenceOrderID]=(5633)
From these results, it is easy to see that the extra work of fabricating the derived table didn't really
buy much in terms of performance. To build this performance query, you needed to know information
about the selectivity of an index that could change if the parameter was less selective. However, with the
simple inclusion of a TOP statement and an ORDER by, you'd be able to keep up with the fanciest T-SQL
programming. This is why the TOP method is preferred over other methods of generating singleton
results. It is easy to use and provides decent performance.