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

Table 9-23: Performance Comparison of Singleton Pattern Selects

Method % Cost CPU Reads Duration

TOP 52 10 4 119ms

MAX1 29 0 6 280ms

MAX2 19 10 4 110ms

Aggregates and CTEs
Frequently requested aggregates from tables should be evaluated for potential covering indexes.
Typically, in an aggregate pattern, you should be able see patterns of aggregation around an already
existing index structure. Take for an example the need to summarize the cost components of a sales order
in the columns: TaxAmt, Freight, and SubTotal by an order date range. If the order date is already a
non-clustered index, then including these columns in the leaf level of the index will allow the optimizer
to seek directly into the non-clustered index. This avoids scanning the much larger clustered index for
these extra columns. Let's look at the problem from the beginning.

You are presented with the requirements to provide a summary by customer of the orders and cost
components in Sales.SalesOrderHeader and provide percentage relationships of these figures across a
period. One brute force way to write the query is like this:

Select CustomerId, Count(*) AS CustOrderCount, Sum(h.TaxAmt) As CustTaxAmt,
Sum(h.SubTotal) As CustSubTotal, Sum(h.Freight) as CustFreight,
Sum(h.TaxAmt)/(Select sum(TaxAmt) From Sales.SalesOrderHeader
where OrderDate > = '07/01/2001'
and orderDate < '12/31/2001') * 100
as PctCustTaxToTotalTax,
Sum(h.Freight)/(Select sum(Freight) From Sales.SalesOrderHeader
Where OrderDate > = '07/01/2001'
and orderDate < '12/31/2001') * 100
as PctFreightToTotalFreight,
CONVERT(DECIMAL(5,2),(Count(*)/
(Select 1.*Count(*) From Sales.SalesOrderHeader
Where OrderDate > = '07/01/2001'
and orderDate < '12/31/2001') * 100))
as PctCustOrderCntToTotalCnt
from Sales.SalesOrderHeader h
Where OrderDate > = '07/01/2001' And OrderDate < '12/31/2001'
Group By CustomerId

Note that the predicate is using OrderDate for a filter and is grouping by customerid .Thereisanon-
clustered index on customer, but no index on OrderDate . You'll see this evidenced by the four table scan
operations for each of the times Sales.SalesOrderHeader is filtered by the order date range. The scan
operations on OrderDate are labeled in Figure 9-15 as 1-4. All the other operations don't involve the use
of any indexes, so you know that the non-clustered index on customer is not even used.