, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Performance and Optimization – How to write optimized queries

In this post we will talk about some precautions we can take while writing queries. Precaution is always better than cure. If we can take care of following things then we can definitely write queries which are optimizer friendly. Some of the points are-

1. Where Clause
Keep your columns in where clause clean. Do not put any functions around columns in the where clause. If you put any functions around the columns in the where clause your query will get a performance hit. Optimizer will give you a scan instead of a seek. And when the table grows people will start complaining that the query is slow. https://msbiskills.com/2015/06/19/avoid-using-function-in-where-clause-why/

2. Join
Keep your columns in join clause clean. Do not put any functions around columns on join conditions.

3. DataTypes
When ever you are joining two tables then always use columns with same datatypes in the joins clause. Also use same data type of scalars while comparison with columns in the where clause. If you don’t the SQL Server will up cast the lower data type to the upper one and you will get an implicit conversion, which is bad. For details please check https://msbiskills.com/2015/06/26/implicit-conversions-are-bad-for-performances-why/

4. Join Predicate
Always have a join predicate, other wise you will end up with cross joining two table and you will get an warning in the execution plan

5. Try to avoid Order by
Try to avoid an order clause on columns that don’t have indexes. Otherwise we will have to pay for Sort operator in the execution plan. If possible use Top with order by. Top with order by is fine. After the top operator is satisfied it stops the execution.

6. Exists
Always use exists if you are not fetching any columns from the right hand side table. Don’t use IN, they are bad for performance. Exists are good for performance as they use Semi joins internally.

7. Like
If you are using like operator to match string then use like ‘Pawan%’ rather than ‘%Pawan%’, this will give us a scan. Where as ‘Pawan% can give index seek which is great. For details click https://msbiskills.com/2015/06/23/like-is-sargable-how-to-write-better-where-clause-ii-lets-check/

Don’t ever do select * when fetching data from physical table.
For details please click https://msbiskills.com/2015/06/12/dont-ever-do-select-its-really-a-bad-idea-why/

8. Loops, cursors
Don’t use loops or cursors, Even loops are also cursors. You always wanted to insert bunch of rows

9. Nested Views,Triggers
Always try not to nest views and trigger because after a certain level you don’t know what’s going on in your system.

10. Which one is better? Equal to (=), IN, Between, etc.
a. = is better than not equal to (!=)
b. Between is better than IN
c. IN is better than NOT IN

11. Cross Apply
Try to use Cross apply(Corelation) rather than using joins, almost every time it will give you Nested Loop join.

I hope you have enjoyed the article. In the next post I will explain which one you should use – Temp table or table variable for performance ?. Thanks for reading !

-Pawan Khowal