Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 and 2008.

The assertion is that with the following batch:-

IF @parameter = 0
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

SQL Server cannot re-use the execution plan generated because the next execution may need a different branch. This implies that SQL Server will eliminate one branch entirely from execution plan on the basis that for the current execution it can already determine which branch is needed. Is this really true?

In addition what happens in this case:-

IF EXISTS (SELECT ....)
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

where it's not possible to determine in advance which branch will be executed?

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:26

This question came from our site for professional and enthusiast programmers.

4  
Related stackoverflow.com/questions/1510489/… –  Alex K. Apr 26 '10 at 12:33
1  
SQL Server can and does reuse the execution plan as it doesn't consider the branches, only the statements contained in the branches. –  MartinC Dec 28 '11 at 14:56
add comment

5 Answers

The only shortcut will be IF 1 = 1

Both @parameter and EXISTS still requires processing for the "general case" (@parameter = 42 say)

Saying that... what does the actual execution plan say as well as profiler capturing recomplition events? (I dislike estimated plans as per Jao's answer)

share|improve this answer
add comment

SQL Server optimises the process of compilation of the query plan for the stored procedure by ignoring the conditional branches inside the stored procedure. The plan will be generated based on the parameters used for the first execution, this will cause problems if the parameters are different for the branches.

I would place the SQL for each of the branches into their own stored procedure, so that the plan generated is based on the actual usage of parameters for that branch.

share|improve this answer
add comment

Try to display the estimated execution plan, not the actual. You'll see that first one contains COND operator.

share|improve this answer
    
And the COND operator tells me what? –  AnthonyWJones Apr 27 '10 at 8:21
    
This operator was included in the cached execution plan too. In your example estimated excution plan will contain one COND operator and 2 SELECT branches. And therefore will be fully reusable. Because when executing a batch sql server evaluates not only DML statements but all other as well, obtaining them from the plan. Internally execution plan is a structure similar to expressions tree. –  Jao May 8 '10 at 6:21
add comment

Maybe its been improved in 2005 and 2008, but using conditionals in 2000 would more likely be worse than you describe, it would compile a plan to best handle the first run of the procedure and then use that plan to execute the procedure even when the conditions changed. In my experience this caused queries that ran in minutes to run in hours. Although I use 2008 now and have used 2005 I can't comment on how the coditionals work there since I no longer use them.

share|improve this answer
2  
2005+ has statement level recompilation so you don't have "one plan per sp" all the time any more –  gbn Aug 24 '11 at 9:20
add comment

Plans will be created based on the parameters passed so in reality I'd say No - having conditional logic which is normally based on parameters is not detrimental to performance.

You will get multiple plans produced, assuming the parameters cause enough of a variance for the query optimizer to notice.

You can see which by switching on the Show Execution plan, running the scripts - notice the differences in the plan. When you run the procedures (I am assuming stored procedures here), you will notice the first-time is generally quicker, the second hit uses the stored plan. Change parameters and repeat then run the original parameters - in theory the plan will still be in cache but it does depend on server usage (cache ticks - they dont stay forever..) etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.