INF: Writing Efficient Queries and Stored Procedures

  - Microsoft SQL Server versions 4.2x for 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...

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."