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

Tuning T-SQL Common Patterns or Idioms
If you've ever felt like you've seen that performance problem before, you've probably tapped into your
mental storage of the pattern and the resulting solution. Some T-SQL performance issues are recurring
issues, and identifying them quickly can greatly reduce your troubleshooting work load. This section
examines performance issues with some common patterns that are used in T-SQL to solve development
problems.
Singleton SELECT Statements
This pattern involves selecting one and only one row from a table. The term singleton refers to a set
with only one element — in the database world this is one row. Typically, there are two approaches
to generating a singleton-type result. One option is to use the TOP statement to lift the top one (1) row
based on a predicate and sort order. A second option is use a derived table to find the one clustered index
value matching the applied predicate using an aggregate function. To tune these types of queries, look
at the results you need from the operation. If you are returning a set of columns that can't be covered
by indexes, then the column values must be looked up from the base pages. It is more efficient to look
up only those rows that meet one condition rather than look up all the rows to determine the top row.
Conversely, if all you need is one row with the column that is in an index, either way will perform about
the same.
The TOP approach is most commonly used, because it is the most idiomatic way to grab one or more
rows. T-SQL provides the language extension and simply adding the TOP statement to an existing query
converts it into a singleton select. In the real world, there are more WHERE conditions than there are
indexes, so this example uses several columns: TransactionDate, Quantity, and ActualCost columns that
aren't indexed. The other columns are indexed.
SELECT TOP 1 transactionId
FROM production.transactionhistoryarchive
WHERE ProductId = 399
AND referenceOrderId = 5633
AND transactionDate = '2001-11-18 00:00:00.000'
AND quantity > 2
AND actualcost > =0
ORDER BY transactionId DESC
It is important to show a query like this in some examples instead of only using the transactionId in
the predicate to show the actions that SQL Server 2005 performs to filter on the additional non-indexed
fields. The optimizer will determine that one of the indexed fields is selective enough to resolve to a small
number of rows and then uses the clustered indexes stored on matching leaves to look up the remaining
key fields. These two actions are seen in the first two operational steps of Figure 9-14, where the plan
shows seeking the index on referenceOrderId and then performing the Key Lookup.
The reason for pointing this out is to show how intelligent the optimizer is at selecting the best index
for the job and how that ultimately results in faster queries. Note that the remaining tasks of applying
the filters to the lookup fields and grabbing the top row almost comes free. The selectivity of using the
referenceOrderId field reduced the first inquiry to one row. If the ProductId index had been used, the
results would have been narrowed to 786 rows. This choice by the optimizer results in a reduction of 785
additional lookup operations whether they occurred via an additional index seek or not.