SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Nested If Then Else vs. Multiple If's
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thekeane
Starting Member

7 Posts

Posted - 08/02/2005 :  16:51:26  Show Profile  Visit thekeane's Homepage  Send thekeane an AOL message  Reply with Quote
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

USA
384 Posts

Posted - 08/02/2005 :  17:09:36  Show Profile  Reply with Quote
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 - 08/02/2005 :  17:24:03  Show Profile  Visit thekeane's Homepage  Send thekeane an AOL message  Reply with Quote
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 - 08/03/2005 :  12:09:46  Show Profile  Visit thekeane's Homepage  Send thekeane an AOL message  Reply with Quote
Anyone???
Go to Top of Page

DaveGerard
Starting Member

7 Posts

Posted - 08/05/2005 :  01:46:19  Show Profile  Visit DaveGerard's Homepage  Reply with Quote
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

India
22754 Posts

Posted - 08/05/2005 :  01:55:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
384 Posts

Posted - 08/05/2005 :  10:15:54  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/05/2005 :  10:20:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000