SQLConfiguration SQLConfiguration

CRUD Statements  «Prev  Next»
Lesson 14 Optimizing query statements
Objective Understand optimization rules for query statements.
Once you know what kind of indexing will be used, you can decide whether to modify your query statement. One of the things you will look at is whether clustered or non-clustered indexes will be used.
The performance impact of a query will depend on the type of index used and the type of query.

Optimization rules

Once you know the indexing plan for your query, you should consider the following:
  1. If many records are being inserted, a clustered index can hurt performance because SQL Server has to physically reorder the rows so that they are in order.
  2. If SQL Server statistics are out of date, the query optimizer might select an index that is no longer the best candidate to use.
  3. If there is a tremendous amount of data that needs to be inserted, it might make more sense to instruct SQL Server not to maintain statistics for given indexes or columns. For example, if you are inserting one million rows, it might make sense to turn off statistics, insert all the data, then turn statistics back on. This prevents recomputing statistics one million times.
The next lesson will review this module.