Query Optimization Techniques

Query Optimization Techniques

Below Query Optimization Techniques should be taken care of to optimize SQL queries:

  1. Use Index
  2. Aggregate table – Pre-populating tables at higher levels so less amount of data need to be parsed.
  3. Vertical partitioning
  4. Horizontal Partitioning
  5. Denormalization
  6. Server Tuning
  7. Move Queries to stored procedures
  8. Remove unneeded views
  9. Only query what you really need
  10. Sort only when required
  11. You should do as much as possible in the Query or Stored Procedure. Going back and forth is plain stupid. e.g.,
    For i = 1 to 2000


    The original Query involves a lot of network bandwidth and will make your whole system slow. Although this example seems simple, there are more complex examples on that theme. Sometimes, the processing is so great that you think it’s better to do it in the code but it’s probably not.

    Sometimes, your Stored Procedure will be better off creating a temporary table, inserting data in it and returning it than going back and forth 10,000 times. You might have a slower query that saves time on a greater number of records or that saves bandwidth.

  12. Remove Weak Joins
    Wrong:

    Corrected:
  13. Use operator EXISTS, IN and table joins appropriately in your query.
    1. Usually IN has the slowest performance.
    2. IN is efficient when most of the filter criteria is in the sub-query.
    3. EXISTS is efficient when most of the filter criteria is in the main query.

Reasons for slow query

  1. Slow network communication.
  2. Inadequate memory in the server computer, or not enough memory available for DB Server.
  3. Lack of useful statistics
  4. Lack of useful indexes.
  5. Lack of useful indexed views.
  6. Lack of useful data striping.
  7. Lack of useful partitioning.

Share this post

Leave a comment

Your email address will not be published.