Database Optimization

Lesson for the day...

If your a developer your probably working with databases. My thought for the day is about optimizing queries for reporting. Every now and again I run across a report that takes half an hour to an hour to run. There are exceptions, BUT this is usually a pretty good sign that the query that runs the report might run better with some optimization.

There are some great books on database optimization and I am by no means an expert on the subject... however I have been fortunate enough to work with some good developers with a good amount of experience with database "philosophies".

A few concepts that have helped me are...

• You are always going to know more about the data than the database optimizer... meaning you know which fields will be used the optimizer does not. So only include the fields that you need in the query and use indexes.
• If you are using MSSQL use the explanation plan option and find all of the places where you can use indexes on tables... sometimes a well placed conversion can save some processing time.
• Sometimes a well placed subqueries can improve performance. Be creative and look for places that optimization can be improved.

There are more extensive solutions for database optimization, and I recommend doing more research on the subject as it is "an art form" rather than a templatized method. However, I sincerely hope this entry might help a fellow developer in the future. Best Wishes!