If statement in tsql bad idea
-
College of mine tells me that IF statement in stored procedure is not a good idea becuase:
1) it either recompiles stored proc on every use
2) or the sql statement inside the "if" are not cached etc...
Is any of this true. Anoterword, is there any downside in using IF statement in SQL stored proc.
Thanks for your help.
-
I believe your colleague is referring to this:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
You should be aware of the concept, but don't draw any unshakeable conclusions - there are very few absolutes in SQL Server and whether to use IF or not is not one of them. Consider each instance on its merits, and be sure to fully understand what is going on and why.
See the following MSDN article for a full background. You must read this to understand.
http://technet.microsoft.com/en-us/library/cc966425.aspx
Paul
-
spatemp (5/24/2010)
1) it either recompiles stored proc on every use2) or the sql statement inside the "if" are not cached etc....
Neither is true.
What you can get is a form of parameter sniffing. If certain branches if will only be executed for certain parameter values, you can get a case where, if the first compile of the proc has other parameter values, the exec plan for some branches will be a poor one.
It's not a problem with IF statement per se. It has to do with the way the entire proc is compiled and the resulting plan cached, and the parameter sniffing that the optimiser does.
You absolutely cannot make a blanket statement that IF statements are bad. There are certain constructs using if statements that are prone to poor execution plans, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In case you hadn't noticed, that was Gail's blog entry that I pointed you at
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply