Managing the WHERE Clause
Posted on October 15, 2009 by thaonguyen
Filed Under How To | |
The WHERE clause is the place to start your search for SQL you can change to get better performance. You already know what indexes you have. Now look at the optimizer plan for a problem query and see if the indexes are getting used or if the optimizer is searching the table row by row.
Why a Table Scan?
Just because you have an index on a column doesn’t mean your optimizer will use it.
- If the amount of data is trivial (as in the msdpn tables), a search using an index may not be faster than a table scan and the optimizer may choose not to use the index.
- If the query includes all rows in the table (no WHERE clause), the optimizer does a table scan.
- If you’re retrieving a lot of data from the table, an index may give no advantage.
- 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 index unavailable.
- Of these elements, only the last two are under your control.
Leave a Reply