Tips and tricks to optimize SQL queries
1.
If function is a constant define it outside
final query as if it is defined with final query it will run for 1 lakh rows or
more.
2. The functions on fields selected from a complex query with many joins should be taken first as just fields and then with outer query should be performed for using those functions.
3. Check for left joins generally one of the left join is consuming lots of time.
4. Try not to use functions on left side of filter before equal to operator as it consumes more time
5. Format>convert > cast when seen on timing based query
. Not in>not exists when seen on timing based query
. Between > >= and<= when seen on timing based query
. Create indexes on columns used in joins as they improve performance of query. Recommendation is to create indexes not more than 5.
. Keep in mind indexes work from left side to right side.
10. Isnull(field,’##’)!=’##’ runs faster than isnull(field,’’)!=’’ (Check please)
11. Tools for quick output: SQL Format chrome extension for formatting to one line or displaying in ordered manner
12. beyondCompare Tool or Winmerge to compare two text queries for any difference line by line
13. Select * from table1 except select * from table 2 query for checking any outstanding fields values during optimization.
14. Use shift+Alt to select multiple lines vertically.
15. Use explicit conversion instead of implicit conversion when converting datatypes from nvarchar to varchar or vice versa as there is spike on server or load increases on server.
16. If there are tables which are being queries for latest data only create archive tables and load old data into that table so that number of rows being queried regularly reduces thereby improving performance of queries.
17. Checking for missing joins as it slows down the query
18. Use having clause instead of multiple subqueries
19. If there are multiple queries in union clause where only conditions are changing in filters not the table try to use pivot clause and use case when so that hitting the table again and again is reduced.
20. Try not to use table variables instead use temporary tables with indexes
21. Use with(nolock) clause as the main hurdle is that there is table lock feature in T-SQL which prevents a query from using a table if it is locked by another table and slows down or degrades performance of multiple queries.
22. Try to use numeric or int datatypes in joins rather than char or varchar as its faster and uses half internal resources. Varchar>char>numeric>int when considered internal usage of resources.
23. Use filestream instead of pdf or jpg stored in databases if file size is larger than 15 MB/ file.
24. If there are multiple tables in inner joins start with table that has least number of rows. Check with count(*) number of rows in each table joined.
25. Use equal to operator instead of like operator where possible. Like takes more time.
26. Use functions instead of batch of queries for logic like finding age or splitting every row by comma and merging it.
27. Try to use least number or exact number of characters when defining datatypes as every character length occupies some space internally.
28. Avoid using *. Write exact fields needed as server fetches data in 8KB of batches so if there are more fields than to and fro for the server increases thereby degrading the performance.
29. Use execution plan to check whether it’s a seek or scan to identify if there are any missing indexes on tables and finding cost of each filter used, also if there are any implicit conversions.
30. Create common function or procedure when certain common logic is expected among multiple procedures to reduce maintenance. Detail the dynamic data in a table and let main query run in for or while loop to call same query again and again.
2. The functions on fields selected from a complex query with many joins should be taken first as just fields and then with outer query should be performed for using those functions.
3. Check for left joins generally one of the left join is consuming lots of time.
4. Try not to use functions on left side of filter before equal to operator as it consumes more time
5. Format>convert > cast when seen on timing based query
. Not in>not exists when seen on timing based query
. Between > >= and<= when seen on timing based query
. Create indexes on columns used in joins as they improve performance of query. Recommendation is to create indexes not more than 5.
. Keep in mind indexes work from left side to right side.
10. Isnull(field,’##’)!=’##’ runs faster than isnull(field,’’)!=’’ (Check please)
11. Tools for quick output: SQL Format chrome extension for formatting to one line or displaying in ordered manner
12. beyondCompare Tool or Winmerge to compare two text queries for any difference line by line
13. Select * from table1 except select * from table 2 query for checking any outstanding fields values during optimization.
14. Use shift+Alt to select multiple lines vertically.
15. Use explicit conversion instead of implicit conversion when converting datatypes from nvarchar to varchar or vice versa as there is spike on server or load increases on server.
16. If there are tables which are being queries for latest data only create archive tables and load old data into that table so that number of rows being queried regularly reduces thereby improving performance of queries.
17. Checking for missing joins as it slows down the query
18. Use having clause instead of multiple subqueries
19. If there are multiple queries in union clause where only conditions are changing in filters not the table try to use pivot clause and use case when so that hitting the table again and again is reduced.
20. Try not to use table variables instead use temporary tables with indexes
21. Use with(nolock) clause as the main hurdle is that there is table lock feature in T-SQL which prevents a query from using a table if it is locked by another table and slows down or degrades performance of multiple queries.
22. Try to use numeric or int datatypes in joins rather than char or varchar as its faster and uses half internal resources. Varchar>char>numeric>int when considered internal usage of resources.
23. Use filestream instead of pdf or jpg stored in databases if file size is larger than 15 MB/ file.
24. If there are multiple tables in inner joins start with table that has least number of rows. Check with count(*) number of rows in each table joined.
25. Use equal to operator instead of like operator where possible. Like takes more time.
26. Use functions instead of batch of queries for logic like finding age or splitting every row by comma and merging it.
27. Try to use least number or exact number of characters when defining datatypes as every character length occupies some space internally.
28. Avoid using *. Write exact fields needed as server fetches data in 8KB of batches so if there are more fields than to and fro for the server increases thereby degrading the performance.
29. Use execution plan to check whether it’s a seek or scan to identify if there are any missing indexes on tables and finding cost of each filter used, also if there are any implicit conversions.
30. Create common function or procedure when certain common logic is expected among multiple procedures to reduce maintenance. Detail the dynamic data in a table and let main query run in for or while loop to call same query again and again.
Comments
Post a Comment