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

Figure 9-15
If you are forced to write this query without having any input on adding indexing structures, one
thing you could do is reduce the I/O. The key to this is removing redundancy. Noticing some basic pat-
terns, you may realize that you could make the summary operations once and then cross apply the results
to each row to cut the I/O immediately in half. One nice way to do this is with the new common-table
expressions. First, you describe the inner results like a derived table, but better, and you'll explore this
in a second. Then cross-apply these results against the outer summary by customer Id. The plan for
this query, shown in Figure 9-16, is much simpler and shows that the index (marked by labels 1 and 2) is
only being read twice.
With Totals As (
Select Count(*) AS OrderCount, Sum(TaxAmt) As TaxAmt,
Sum(SubTotal) As SubTotal, Sum(Freight) as Freight
from Sales.SalesOrderHeader h
Where OrderDate > = '07/01/2001' And OrderDate < '12/31/2001'
)
Select CustomerId, Count(*) AS CustOrderCount, Sum(h.TaxAmt) As CustTaxAmt,
Sum(h.SubTotal) As CustSubTotal, Sum(h.Freight) as CustFreight,
Sum(h.TaxAmt)/(Totals.TaxAmt) * 100 as PctCustTaxToTotalTax,
Sum(h.Freight)/(Totals.Freight) * 100 as PctFreightToTotalFreight,
CONVERT(DECIMAL(5,2),(Count(*)/(1.*Totals.OrderCount)) * 100)
as PctCustOrderCntToTotalCnt
from Sales.SalesOrderHeader h, Totals
Where OrderDate > = '07/01/2001' And OrderDate < '12/31/2001'
Group By CustomerId, Totals.TaxAmt, Totals.Freight, Totals.OrderCount
Figure 9-16