Stored procedures Performance

[nandhaK]

Hi guys:

I am having a typical problem.

I am having an SP that uses 7 IF Conditions. Same SELECT statement is repeated under all IF Conditions except "where clause" which is actually determined by "if condition".
First time it runs fine with in 30 seconds. Next time When I change the parameter which actually changes "If Condition" , it is taking more than 2mins.Actually whenever I change parameter performance varies between 2
to 10 mins and sometimes it runs for ever. I think whenever there is change in IF Condition , It still tries to use the same execution plan which was created before. This works fine when I used "WITH RECOMPILE "option. However , to boost the performance I don't want to recompile every time ,because this Procedure is called by application more than 100 times. I also don't want to create separate sp for each condition because I have 21 similar Procedures and if I create one procedure for each condition I will end up creating 147 procedures.
Please help me if you have any solution for this with out " with recompile option" or separate Stored procedures.
I appreciate your great help.

Thank you
Nandha

 

[DangerPowers]

The optimal solution is, of course to have separate compiled queries, but I can understand your pain in managing so many objects.

You should probably leave the WITH RECOMPILE option on. Most of the time, a bad query plan is worse than recompiling.

Try this article to see if there is anything else causing recompiles that may help your situation (besides, the site has a wealth of knowledge about SQL Server tuning!):

http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp