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

them back at the checkout lane except the one you want instead of just getting the one box of cereal that
you want in the first place. Getting the cereal you want in the first place is analogous to pushing the
conditions (or predicate) deeper into the plan (your shopping list). A real example would be looking for
the average list prices of products that have product numbers starting with ''VE%.'' Queries that perform
this task with the HAVING clause can be rewritten more efficiently using WHERE clauses.

SELECT prod.ProductNumber, AVG(hist.ListPrice)
FROM Production.Product prod
INNER JOIN Production.ProductListPriceHistory hist
on prod.productId = hist.productId
GROUP BY prod.ProductNumber
HAVING prod.ProductNumber like 'VE%'

This query applies the filter as the last step. You can see this by reading the plan details (abbreviated
for brevity).

HAVING QUERY PLAN
8|--Filter(WHERE:([Product].[ProductNumber] as [prod].[ProductNumber] like N'VE%'))
7 |--Compute Scalar
6 |--Stream Aggregate(GROUP BY:([prod].[ProductNumber])

5 |--Sort(ORDER BY:([prod].[ProductNumber] ASC))

4 |--Merge Join(Inner Join,

3 MERGE:([prod].[ProductID])=([hist].[ProductID])

2 |--Clustered Index Scan [PK_Product_ProductID

1 |--Clustered Index Scan [PK_ProductListPriceHistory_ProductID_StartDate]

(The steps in the plan are numbered for reference. These numbers don't normally appear in a plan). Note
that no filter is applied and the rows that are still in memory at the seventh step are the total number of
summarized rows scanned in the Clustered Index Scan operation at Step 1. The following query rewrites
the statement to be more efficient:

SELECT prod.ProductNumber, AVG(hist.ListPrice)
FROM Production.Product prod
INNER JOIN Production.ProductListPriceHistory hist
on prod.productId = hist.productId
WHERE prod.ProductNumber like 'VE%'
GROUP BY prod.ProductNumber

WHERE QUERY PLAN
5|--Compute Scalar (
4 |--Stream Aggregate(GROUP BY:([ProductNumber])
3 |--Nested Loops(Inner Join, OUTER REFERENCES:([prod].[ProductID]))

2 |--Index Seek(OBJECT:([AK_Product_ProductNumber]),

SEEK:([prod].[ProductNumber] > = N'VE' AND [prod].[ProductNumber] < N'VF'),
WHERE:([prod].[ProductNumber] like N'VE%') ORDERED FORWARD)

1 |--Clustered Index Seek(

OBJECT:( [PK_ProductListPriceHistory_ProductID_StartDate]),
SEEK:([hist].[ProductID]= [ProductID]) ORDERED FORWARD)

Applying the predicate in the WHERE clause reduces the number of pages read from 20 down to 8.
Where did that information about paging come from? If you use the profiler to trace events while you are
running the queries, you can see a metric for page reads in the output. You can also get this information
from one of the new DMVs, sys.dm_exec_query_stats. These new DMVs may even be more preferred