INFO: Writing Efficient Queries and Stored Procedures

This article was previously published under Q46434
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
This article contains some suggestions for writing efficient queriesand stored procedures.
More information
WHERE clauses that direct the index and join order selection should beof 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 forindex selection. Indexes that consist of several columns are usefulonly if the selection columns comprise a prefix of the index (orcompletely match the index).

The "=" (equals) sign is best if a unique index is available. Closedranges 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 nolonger necessary to access the actual data pages.

Do not use expressions or data conversions in index and join-selectionWHERE clauses or the optimizer may not recognize that the expressionevaluates to a constant.

Local variables in WHERE clauses are considered to be "unknown" andare not considered by the optimizer, except for the input parametersof stored procedures.

In versions of SQL Server earlier than 4.2, subqueries are "unwound"into joins by the optimizer. This can introduce duplicates in certainself-joins unless "DISTINCT" is added (prior to version 4.2).

IN lists are treated as multiple OR clauses. BETWEEN is treated as aclosed interval. LIKE with a trailing wild card is treated as a closedinterval.

Be sure your STATISTICS are up-to-date. Remember that storedprocedures are not re-optimized when indexes are added.

SET STATISTICS I/O & TIME ON and SET SHOWPLAN ON can help inunderstanding what the optimizer is doing when processing a particularquery or procedure.

Output from SET SHOWPLAN ON

Table scan means no index was used and the data pages wereblind-searched. The alternative is "index n used" or somethingsimilar. If there was an index that could have been used and wasn't,run UPDATE STATISTICS.

Nested Iteration is the default join technique. The criticalperformance consideration is the order of nesting. The best order isthat 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 producesmultiple values instead of just one like "sum."
4.20 4.2a 4.2b 4.21 Optimization Tuning
Properties

Article ID: 46434 - Last Review: 11/01/2013 20:59:00 - Revision: 2.0

  • Microsoft SQL Server 4.21a Standard Edition
  • kbnosurvey kbarchive kbinfo kbusage KB46434
Feedback