Hi, In your query you are trying a lot of stuff

1. Creating a Lot of Temp Tables and Indexes.
2. Using these Temp Tables to join/filter with your main query
3. Drop your Temp tables

SQL Server Depends on statistics and Indexes to generate an execution plan. When these conditions change the execution plan becomes invalid. As and when you drop the temp tables the execution plans become invalid and it needs to be recompiled.
Additionally, You are using a lot of Optional parameters and using 'OR' to join various conditions. This is one of the killing situations. You may have to try with IF conditions to make them choose the correct plan.
In situations like this I have tried these options.

• Instead of Temp tables I have used Permanant tables. I have added an additional key SPID to make sure I do not read someone else's data.
• I seperate different scenarios and write different SQL statements for them. Yes, it is a pain for developer from maintainability point of view, but you will, not only increase the performance but also make the code easier to debug

Hope this helps!

Thanks Preethi

For the reply

How would I re-write the query to remove the OR to make the query more efficient?

Also I am interested in the permanent table, this is not something I have though about

Can you please give me an example of how to write different scenarios and write different SQL statements for them

And how to create a permanant table with a SPID

Thanks  for your help

 

 

To the Moderators/Administrator,
As most of the posts have been removed, it is not worth having the thread. It is just wasting space.
Either we should restore the posts back, (which is useful for others to search on) or remove this thread completely. It is also generates another question, Can we allow people to remove the entire post?