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

Select CustomerId, Count(*) AS CustOrderCount, Sum(TaxAmt) As CustTaxAmt,
Sum(SubTotal) As CustSubTotal, Sum(Freight) as CustFreight
from Sales.SalesOrderHeader h
Where OrderDate > = '07/01/2001' And OrderDate < '12/31/2001'
Group By CustomerId
)
Select CustTotals.*,
CustTotals.CustTaxAmt/Totals.TaxAmt as PctCustTaxToTotalTax,
CustTotals.CustFreight/Totals.Freight as PctFreightToTotalFreight,
CONVERT(DECIMAL(5,2),(CustTotals.CustOrderCount/(1.*Totals.OrderCount))
* 100) as PctCustOrderCntToTotalCnt
From CustTotals,
(Select Sum(CustOrderCount) as OrderCount,
Sum(CustTaxAmt) As TaxAmt,
Sum(CustSubTotal) As SubTotal,
Sum(CustFreight) as Freight
from CustTotals) Totals
You can query the base table once to build the CustTotals derivation. Then later, without calling the
base table again, you can query the CustTotals derivation again and aggregate at a totals level. It looks
as if you could really get the I/O operations down to one pass of the index. Unfortunately, although this
capability is high on the cool factor and may be easy to read, the optimizer is not fooled. It still seeks
into the non-clustered index on OrderDate twice. You can see this by reviewing the Showplan Statistics
Profile (abbreviated here to show part of statement text and output list):
Index Seek(OBJECT:([IX_SalesOrderHeader_OrderDate] AS [h]), SEEK:([h].[OrderDate]
> = '2001-07-01' AND [h].[OrderDate] < '2001-12-31') ORDERED FORWARD)
[h].[CustomerID], [h].[TaxAmt], [h].[Freight]
Index Seek(OBJECT:([IX_SalesOrderHeader_OrderDate] AS [h]), SEEK:([h].[OrderDate]
> = '2001-07-01' AND [h].[OrderDate] < '2001-12-31') ORDERED FORWARD)
[h].[CustomerID], [h].[SubTotal], [h].[TaxAmt], [h].[Freight]
You can see the first seek on the bottom being done to output the four columns needed by the CTE; the
second seek is being done for the three columns needed by the summary of the CTE. The subtree costs
are also a bit higher at .0968517, making this solution not as good as the first CTE, but interesting.
Derived Tables and Correlated Subqueries
This is a common idiom where a DISTINCT clause or other aggregate operation is used to summarize
parent-level data after applying WHERE predicates at the child-relationship level. In the best possible
case, the filter and the columns under summation would be within clustered or covered indexes. How-
ever, a more typical example would be a query in the AW database that returns unique corresponding
Purchase Order Numbers when provided a Carrier Tracking Number, even though a Carrier Tracking
Number may be duplicated in the child-level, and neither item has an index whatsoever. In this instance,
the query to perform this task could be written like this:
SELECT DISTINCT PurchaseOrderNumber
FROM Sales.SalesOrderHeader h
INNER JOIN sales.SalesOrderDetail d
ON h.SalesOrderId = d.SalesOrderId
WHERE d.CarrierTrackingNumber = N'E257-40A1-A3'