ID Number: Q46434
1.10 1.11 4.20
OS/2
Summary:
This article contains some suggestions for writing efficient queries
and stored procedures.
More Information:
WHERE clauses that direct the index and join order selection should be
of the following form:
<col> <operator> <constant> AND... (all cols from same table)
<col> <operator> <col> AND... (join criteria)
<col> <operator> <constant> OR...
WHERE clauses containing NOT are not useful to the optimizer for
index selection. Indexes that consist of several columns are useful
only if the selection columns comprise a prefix of the index (or
completely match the index).
The "=" (equals) sign is best if a unique index is available. Closed
ranges are next ( col1>5 AND col1<10 ); open intervals are next
( c1>5 ).
Higher priority will be given to indexes that "cover" the query
(include all of the columns in the select list) because it is then no
longer necessary to access the actual data pages.
Do not use expressions or data conversions in index and join-selection
WHERE clauses or the optimizer may not recognize that the expression
evaluates to a constant.
Local variables in WHERE clauses are considered to be "unknown" and
are not considered by the optimizer, except for the input parameters
of stored procedures.
In versions of SQL Server earlier than 4.2, subqueries are "unwound"
into joins by the optimizer. This can introduce duplicates in certain
self-joins unless "DISTINCT" is added (prior to version 4.2).
IN lists are treated as multiple OR clauses. BETWEEN is treated as a
closed interval. LIKE with a trailing wild card is treated as a closed
interval.
Be sure your STATISTICS are up to date. Remember that stored
procedures are not re-optimized when indexes are added.
SET STATISTICS I/O & TIME ON and SET SHOWPLAN ON can help in
understanding what the optimizer is doing when processing a particular
query or procedure.
Output from SET SHOWPLAN ON
---------------------------
Table scan means no index was used and the data pages were
blind-searched. The alternative is "index n used" or something
similar. If there was an index that could have been used and wasn't,
run UPDATE STATISTICS.
Nested Iteration is the default join technique. The critical
performance consideration is the order of nesting. The best order is
that which minimizes physical I/O. The alternative join technique is
"reformat," which is really a match/merge.
The term "Vector aggregate" is an aggregate function that produces
multiple values instead of just one like "sum."
Additional referenced words: Optimization and tuning