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

You can see that the first plan results in an Index Scan operation when the WHERE predicate is "WHERE
Year(OrderDate) = 2001 AND Month(OrderDate) = 7" . Anytime you perform a function against a column,
every row must first be evaluated by the function into a scalar result before it can be retested for the
comparison. Naturally, examining every row creates the scanning activity. By rearranging the
predicate to "WHERE OrderDate BETWEEN '07/01/2001' AND '07/31/2001'" an intermediate result does
not have to be calculated to be tested for a comparison. Instead, the index can be examined directly. You
can see the difference that a simple rearrangement makes in Table 9-15. The subtree cost difference is
huge, most of which is eaten up with reading 61 pages compared to the 2-page reads of the index seek.
A query like this is I/O intensive and if it is causing I/O problems on your server, consider this a gift.
You can easily rearrange the predicate and reap the performance benefits, but don't get too excited. This
is the low-hanging fruit in the performance tuning world and you won't see these often.

Table 9-15: Costs of Scan versus Seek Operations

Method SubTree Cost CPU Reads Duration

Scan 0.0808565 30 61 250ms

Seek 0.00347912 10 2 95ms

Although rewriting predicates into searchable arguments is the best policy, you won't always achieve
the stellar results you see in this example. If the column being searched does not have an index or
if the evaluation is too complex, functionally the table scan may be the most efficient way to resolve
the query. An example can be seen by evaluating the methods of examining an account number with a
scanning LEFT() function to a LIKE operator using the pattern matching string '10-40[^2]%' . Evaluate
these two queries:

SELECT SalesOrderId FROM Sales.SalesOrderHeader
WHERE CHARINDEX('10-403', AccountNumber) = 0

SELECT SalesOrderId FROM Sales.SalesOrderHeader
WHERE AccountNumber like '10-40[^3]%'

In the query plan in Figure 9-12, the first thing you'll notice is that even though the Index Seek is being
achieved, the total cost margin between the two methods, 88 percent and 12 percent, is not as dramatic
as the first rewrite example.

String manipulation is not the strength of SQL Server, so even without the benefit of query plan
estimations you'd expect to see more time taken in the CPU for these operations. What you might not
expect is that under these conditions, you'll still incur an evaluation against every column value in the
table. You can see the increased CPU usage time in Table 9-16 for the Seek method, and the suprising fact
that both methods incur the exact same number of page read operations.

The duration numbers also underscore that in some circumstances, the evaluation is faster against all
the column values instead of against a range of values. This is an important point to remember. The
execution plan numbers reflect expected costs not speed. So if it is speed you are after, you always need
to compare the duration numbers in the profiler.