Database Reference
In-Depth Information
Figure 18-7.
Execution plan showing the detrimental effect of an arithmetic operator on a
WHERE
clause column
The following are the corresponding
STATISTICS IO
and
TIME
outputs.
*
operator on the
PurchaseOrderID
column:
•
With the
Table 'PurchaseOrderHeader. Scan count
1
, logical reads 11 CPU time = 0 ms,
elapsed time = 61 ms.
PurchaseOrderID
column:
•
With no operator on the
Table 'PurchaseOrderHeader. Scan count 0, logical reads 2 CPU time = 0 ms,
elapsed time =27 ms.
Therefore, to use the indexes effectively and improve query performance, avoid using arithmetic operators on
column(s) in the
WHERE
clause or
JOIN
criteria when that expression is expected to work with an index.
Worth noting in the queries shown in Figure
18-7
, both queries were simple enough to qualify for
parameterization as indicated by the
@1
and
@2
in the queries instead of the values supplied.
■
For small result sets, even though an index seek is usually a better data-retrieval strategy than a table scan
(or a complete clustered index scan), for small tables (in which all data rows fit on one page) a table scan can be cheaper.
i explain this in more detail in Chapter 8.
Note
