In this tip, from the book "Practical SQL: The Sequel" (Addison-Wesley, 2001), Judith Bowman examines the effects of bad WHERE clauses:
WHERE clauses are a possible place to start to improve query performance. Often the place to look is your indexes. Of the possible problems with WHERE clauses, only two are under your control.
- If the optimizer does not have accurate information on data distribution, it may pick the wrong index or no index at all.
- If you include certain elements in the WHERE clause, you may make the indexes unavailable.
Data distribution statistics
First, check your system documentation to see if the DBA needs periodically to run a command to keep the optimizer current (UPDATE STATISTICS in Transact-SQL and Informix, ANALYZE in Oracle.) Microsoft SQL Server also supports a command that tells you when the command was last run (STAT_DATE). See below for a list of commands associated with index statistics. The SQL Anywhere ESTIMATE command is included in the table, but it works differently than the UPDATE STATISTICS or ANALYZE commands, allowing the user to give the optimizer hints on data distribution.
- SQL Anywhere: ESTIMATE
- SAE: UPDATE STATISTICS
- MS SQL Server: UPDATE STATISTICSSTATS_DATE
- Oracle: ANALYZE
- Informix: UPDATE STATISTICS
Disabling an index with a bad WHERE
Don't disable a valid index by the way you construct your WHERE clause. The easiest elements to optimize are comparison operators (=, >, <, and variants) or operators that can be translated to comparison operators (BETWEEN and some LIKE clauses).
Anything else may make your indexes unavailable. Here are some suspicious areas to investigate. Since architecture and optimizers vary so much, you'll have to check your system documentation to find out just how these areas affect (or don't affect!) your queries.
- Comparing columns in the same table
- Choosing columns with low selectivity indexes
- Doing math on a column before comparing it to a constant
- Applying a function to column data before comparing it to a constant
- Finding ranges with BETWEEN
- Matching with LIKE
- Comparing to NULL
- Negating with NOT
- Converting values
- Using OR
- Finding sets of values with IN
- Using multicolumn indexes
To learn more about SQL, buy the book Practical SQL: The Sequel
For More Information
- What do you think about this tip? E-mail us at [email protected] with your feedback.
- The best SQL Web Links.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL gurus are waiting to answer your toughest questions.