November 7, 2010
Sql Query Optimization
Question
Suggest few areas to inspect to improve the performance of a slow query
Answer
Here are few areas to check:
- Tables have no indexes. Database indexes improve query performance dramatically on the expense of slow writes and extra space
- Tables are scanned in full. Scanning huge tables degrades performance
- Table statistics are not updated. Statistics are used by query optimizer to come up with the best execution plan
- Query gets blocked because another process holds locks for an extended period of time
- Stored procedures are recompiled excessively
- If the query contains unnecessarily complicated joins then you need to think about optimizing your query
- 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
- Excess usage of cursors and temporary tables.