SQL Server Performance Forum – Threads Archive

Procedure Recompile Question

I have stored procedure which has if else block .
I want to know whether if else causes recompilation of procedure. Thanks in advance

Hi ya, no, if else does not cause a procedure recompilation in SQL2000 or later Cheers
Twan

What happen if else block executes different query.I read somewhere that
procedure execution plan is made and save for the path taken so when next time
procedure executed with different parameters which causes another block to executed
then procedure recompile .I m not sure about that why i make this post Thanks
Vijay

I remember I read the same somewhere, however if you read microsoft article about recompilation scenarios you won’t find that. I had the same discussion with Twan a year ago. I tested the scenario and it looks like that article (one claiming if-else structure causes recompilations) was wrong (which means I was wrong too). However, it may happen that stored procedure will be recompiled in case procedure parameter value is a part of "if criteria" or appears in either branch. The reason for recompilation is the general rule that execution with different parameter value may cause recompilation. The decision algorthm is not published and it could change with each major release and service pack anyway. My conclusion is: It is good practice to execute each branch of ""if else" structure as a separate stored procedure if there is code inside that can have different execution plan, but for different reason then the scenario mentioned.

Hi ya, yeah there has always been confusion about whether an if/else construct in a procedure causes a recompile, but no the MS compiler does not run the proc to see which bits of it will be run before it compiles it. So every sql statement in a proc will be compiled the first time the proc is pulled into the proc cache. This has a benefit in that the compilation can be used next time without having to worry about a different path being taken through the proc BUT it also has drawbacks…
– if there are a large number of statements that only get executed sometimes, then you might be better of putting them into a sub proc and calling that from the parent proc
– if there are sql statements which will return castly different number of rows, e.g. where a date range is passed into the proc you can sometimes get better performance by either using with recompile, or by creating a master proc which checks how many days there are between the start and end date and if greater than some threshold it calls the proc which was saved with one name, otherwise is calls the same proc but saved under another name e.g. if datediff( day, @start, @end ) > 100
exec proc_get_stuff_large_range @start, @end
else
exec proc_get_stuff_small_range @start, @end
where both of the stored procs actually contain the same code. Slight hassle for maintenance but can make a huge difference in performance if the second is called 90% of the time and has a good index on the date field Cheers
Twan

forgot to say stored proc parameters will also never cause a proc to recompile, however the parameter values when the proc was first pulled into the cache are used to determine the ‘best’ query plan (parameter sniffing discussed on this and other forums in detail) the conditions which cause recompiles in 2000 are temp table creation
data changes in tables, there are thresholds which are generally percentage based I think
first use of unresolved names, e.g. if proc has an externally declared temp table, then the first time that table is used within the proc it will recompile in sql2005 individual statements within a proc will be recompiled rather than the entire proc Cheers
Twan

Here is the article:http://support.microsoft.com/default.aspx?scid=kb;en-us;243586#kb2 Have I halucinating again or parameters were the cause for recompilation before? Last article revision is march 2005. Anyway, obviously at least from the march 2005 sp parameters don’t affect sp recompilation.

Here is the link for sql server 2005:http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

]]>