Sql Query Optimization

Question

Suggest few areas to inspect to improve the performance of a slow query

SQL query optimization

Answer

Here are few areas to check:

  1. Tables have no indexes. Database indexes improve query performance dramatically on the expense of slow writes and extra space
  2. Tables are scanned in full. Scanning huge tables degrades performance
  3. Table statistics are not updated. Statistics are used by query optimizer to come up with the best execution plan
  4. Query gets blocked because another process holds locks for an extended period of time
  5. Stored procedures are recompiled excessively
  6. If the query contains unnecessarily complicated joins then you need to think about optimizing your query
  7. Normalization is need to maintain database integrity but if we have too much normalization this might impact performance so we should compromise integrity for boosting performance
  8. Excess usage of cursors and temporary tables.

Leave a Reply