1. Keep in mind that when you write a Store Procedure Sql Server generates an SQL plan. If you happen to write a Store Proc and have used an "OR" Sql Server will generate only one plan, therefore there will be no choosing which plan is better.
- Do not use "OR" in a query instead the best solution is to use "IF","Else", check for one used case if true then run the query else run the other one.
- Move conditonal logic outside the query in order for the SQL Server to generate another Execution plan to compare on the fastest.
- If you are worried of using more IF Else blocks of code then Dynamic Sql to write your querries.
SQL Execution Plan
2. When running Sql query click the actual plan on the SQL Server Management to see the Plan SQL Server gerated for you. If there are warning s you will see them as well as suggestions in some cases.
- When coding Store Proc make sure you are NOT using Temp DB, else query wont scale. Every time the query runs SQL Server has to query the Temp Database that could be overloaded by other database queries, in the traffic heavy enviroment like the production, everytime the Temp Database is hit, there is a chance of taking long to respond. Solution is make sure to look at the execution plan and make sure that the temp database is avoided by all means.
2. Optimizing a query with an OR, you could write a function like so:
Create OR Alter Function()
Return Table as
Return (--Then write your query here)
- The query above is called writing inline Sql which is much faster.
you could then use the inline function against other queries.