Query Optimization Techniques
Below Query Optimization Techniques should be taken care of to optimize SQL queries:
- Use Index
- Aggregate table – Pre-populating tables at higher levels so less amount of data need to be parsed.
- Vertical partitioning
- Horizontal Partitioning
- Server Tuning
- Move Queries to stored procedures
- Remove unneeded views
- Only query what you really need
- Sort only when required
- 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 20001Select salary From Employees Where EmpID = Parameter(i);1Select salary From Employees Where EmpID >= 1 and EmpID <= 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.
- Remove Weak Joins
Wrong:123Select D.Text As ProductName, P.PriceFrom Products P INNER JOIN Description D On P.DescID = D.DescIDWhere D.LangID = 1
Corrected:123Select D.Text As ProductName, P.PriceFrom (Select DescID, Text From Description Where D.LangID = 1) DINNER JOIN Products P On D.DescID = P.DescID
- Use operator EXISTS, IN and table joins appropriately in your query.
- Usually IN has the slowest performance.
- IN is efficient when most of the filter criteria is in the sub-query.
- EXISTS is efficient when most of the filter criteria is in the main query.
Reasons for slow query
- Slow network communication.
- Inadequate memory in the server computer, or not enough memory available for DB Server.
- Lack of useful statistics
- Lack of useful indexes.
- Lack of useful indexed views.
- Lack of useful data striping.
- Lack of useful partitioning.