Some Query Optimization Techniques

Performance of Query is most essential thing in project.

To increase the performance, complicated queries  need to be optimized.

Here, i posted some techniques to optimize complicated queries. Try with this techniques, it will definitely improves the performance.

Use 'With no lock'  if the query is much complicated. Used to avoid Dead Lock situations.

Use '<= '  and '>='  instead of 'Between'  and 'And'.

Make sure there is a need for any String Manipulation Functions in the SP, such as Ltrim, Rtrim, Substring, Cast, Convert etc…

Mostly try to eliminate Dynamic Queries.

Try to Merge 'If' conditions using 'And/Or',  if there are more 'If'  conditions in the SP.

In the 'Where'  clause please use the least number of record output condition at the top.

Try to not use any 'Order By' if not needed.

Do not use any 'sub queries'  instead use 'Joins'.

Do not use 'In/Not In'  functions instead use 'Or/And'.

Check whether all the fields in the select statement are being used in the report if not please remove them from SP.

Please check the Execution Plan after making the changes to find out the difference between the usage.