SQL Server Performance Forum – Threads Archive

Number of if conditions

In stored procedure around 30-40 number of If conditions to check the value of same variable will slow down the performance? If yes how much? e.q.
If (@sort = ‘MD’))
Begin
Select @Ord = ‘Modeling’
End
If (@sort = ‘MA’))
Begin
Select @Ord = ‘Marketing’
End
etc.. etc.. Suggest if any other solution? Case is the one but I couldn’t find out the performance difference between IF and CASE in SQL Server.

---

There is not much difference in using between if and select Case.
as far as not many sql string related functions or other are being used,
then i feel it will not hurt the performance much.

---

Read http://www.sql-server-performance.com/stored_procedures.asp it says Another, much better performing way, although it will take you more time to code, is to include IF…ELSE logic in your stored procedure, and create separate queries for each possible combination of parameters that are to be submitted to the stored procedure. This way, you can be sure you query is as efficient as possible each time it runs.

---

Where I can find out which Control-of-Flow keyword is better in which scenario or comparison between two Control-of-Flow keywords?

---

declare @Ord varchar(10)<br />declare @sort varchar(10)<br />set @sort=’MA'<br />select @Ord=case @sort when ‘MD’ then ‘Modeling'<br /> when ‘MA’ then ‘Marketing'<br /> else ”<br /> end<br /> <br /> print @Ord<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by surendrakalekar</i><br /><br />In stored procedure around 30-40 number of If conditions to check the value of same variable will slow down the performance? If yes how much? e.q.<br />If (@sort = ‘MD’))<br />Begin<br /> Select @Ord = ‘Modeling'<br />End<br />If (@sort = ‘MA’))<br />Begin<br /> Select @Ord = ‘Marketing'<br />End<br />etc.. etc..<br /><br />Suggest if any other solution? Case is the one but I couldn’t find out the performance difference between IF and CASE in SQL Server.<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

---

If (@sort = ‘MD’))
Begin
Select @Ord = ‘Modeling’
End
If (@sort = ‘MA’))
Begin
Select @Ord = ‘Marketing’
End
etc.. etc..
The problem with the above first and foremost is you have multiple if statements and no else conditioning. This means all IF statements are ran until you get thru them. CASE and IF…ELSE are the same in functionality but CASE offers returns more like a function than an IF statement so the codeing can be simplified overall. Ex IF..ELSE IF @sort = ‘MD’
BEGIN
SET @Ord = ‘Modeling’
END
ELSE
IF (@sort = ‘MA’))
BEGIN
SET @Ord = ‘Marketing’
END
ELSE
IF (….)
With a Case however you can do this. SET @Ord = (SELECT CASE @sort
WHEN ‘MD’ THEN ‘Modeling’
WHEN ‘MA’ THEN ‘Marketing’
WHEN …..
END) So overall the readability and length of the code is much better than the IF…ELSE Also because IF…ELSE and CASE both can take advantage of short-circuiting the code completes on the first match as opposed to having to check all values. However I do suspect you are writing dynamic sql code later on and you may not be actually approaching the code optimally for your situation. Can you be more specific in the details of what you are trying to do, how you are using it and how much data will be returned at a time? You might actually find that your approach is incorrect and someone have a better method to assist you.

---

Hi antares686,
Converting if conditions to Case is not a problem but before that I would like to know how much will be effective or not at all. For us performance will be first choice than readability. You can have a look, one of our sp posted in this forum. see below link.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7864
This sp also contains so many if’s. My question is if I convert it into Case will it improve performance? If yes explain how much?
Where I can find out which Control-of-Flow keyword is better in which scenario or comparison between two Control-of-Flow keywords?

---

Just out of curiosity. Did you have a look at the link I’ve posted in your other thread? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Between CASE and IF..ELSE done properly it would be roughly the same.
However if you continue to use IF multiple times with no else nesting it depends on the number of executions over a period of time and many other factors to be measurable.

---

]]>