Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Nested If Then Else vs. Multiple If's

Author  Topic 

thekeane
Starting Member

7 Posts

Posted - 2005-08-02 : 16:51:26
I just assumed control of a stored procedure in which the previous author used about 40 if statements to check various conditions. And in several groups the same base set of conditions is used. For Example 10-15 begin with "if color='blue' and.." and another 15 begin with "if color='red' and..." and within these groups there are also several subgroups of conditions.

This seems rather inefficent to me and as a result my question is that do you think that grouping these statements under blanket if clauses will actually speed up the stored procedure? I really want to do this but it would be hard to justify the work and risk if it really won't help at all.

All you optimizers out there, would love some input.

thx

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-02 : 17:09:36
I've been taught that when a stored procedure has conditional clauses that cause different execution paths, that it basically has to recompile the stored proc each time it runs. Which defeats one of the benefits of stored procedures.

For example:
IF @Color = 'Red'
BEGIN
IF @Flavor = 'Sweet'
BEGIN
SELECT *
FROM tblRedSweetStuff
END
ELSE
BEGIN
SELECT *
FROM tblRedStuffThatIsntSweet
END
END
ELSE
BEGIN
SELECT *
FROM tblNotRedStuff
END

Now, if within each of these conditionals a function is being called which executes the same block of code, then I don't think that there is such a big hit in optimization. I could be wrong on that though, someone else with more optimization experience might want to chime in.

Aj
Go to Top of Page

thekeane
Starting Member

7 Posts

Posted - 2005-08-02 : 17:24:03
Just to wanted add more some more detail to the explanation, using the above mock up as a model, the current state is like what is below. The question is should I make it more like the one in AJ's post and is it worth it.

IF @Color = 'Red' AND @Flavor = 'Sweet'
BEGIN
SELECT * FROM tblRedSweetStuff
Select @Flavor ='Good'
END

IF @Color = 'Red' AND @Flavor <> 'Sweet'
BEGIN
SELECT * FROM tblRedStuffThatIsntSweet
Select @Flavor = 'Bad'
END

IF @Color <> 'Red' AND @Flavor = 'Sweet'
BEGIN
SELECT * FROM tblNotRedSweetStuff
Select @Flavor = 'Good'
END

IF @Color <> 'Red' AND @Flavor <> 'Sweet'
BEGIN
SELECT * FROM tblNotRedStuff
Select @Flavor = 'OK'
END
Go to Top of Page

thekeane
Starting Member

7 Posts

Posted - 2005-08-03 : 12:09:46
Anyone???
Go to Top of Page

DaveGerard
Starting Member

7 Posts

Posted - 2005-08-05 : 01:46:19
select case @Color
  case 'Red'

    select case @Flavor

      case 'Sweet'
      sql = "select * from tblRedSweetStuff"

      case else
      sql = "select * from tblRedStuffThatIsntSweet"

    end select

  case else

    sql = "select * from tblNotRedStuff"

end select

set rs = cn.execute(sql)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 01:55:52
If there are too many if statements then you can handle it in your presentation layer as suggested above

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-05 : 10:15:54
So what performance gain would be seen if he went with something like this:


DECLARE @txtSQL varchar(8000)

CASE WHEN (@Color = 'Red' AND @Flavor = 'Sweet')THEN
SET @txtSQL = 'SELECT * FROM tblSweetStuff'
WHEN (@Color = 'Red' AND @Flavor = 'Bitter') THEN
SET @txtSQL = 'SELECT * FROM tblRedBitterStuff'
ELSE
SET @txtSQL = 'SELECT * FROM tblAnyFlavor'
END

EXECUTE (@txtSQL)



Would this cause the sp to recompile since the execution is the same (EXECUTE @txtSQL)? Or does it still cause recompile since there are conditional statements?

Aj

Hey, it compiles.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 10:20:12
I don't see

SELECT * FROM tblAnyFlavor

getting into the QUery Plan Cache any time soon

If performance were important I'd opt for:

IF @Color = 'Red' AND @Flavor = 'Sweet'
BEGIN
EXEC dbo.usp_RedSweetStuff
END

IF @Color = 'Red' AND @Flavor <> 'Sweet'
BEGIN
EXEC dbo.usp_RedStuffThatIsntSweet
END

IF @Color <> 'Red' AND @Flavor = 'Sweet'
BEGIN
EXEC dbo.usp_NotRedSweetStuff
END

IF @Color <> 'Red' AND @Flavor <> 'Sweet'
BEGIN
EXEC dbo.usp_NotRedStuff
END

Kristen
Go to Top of Page
   

- Advertisement -