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.
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 ENDELSEBEGIN SELECT * FROM tblNotRedStuffENDNow, 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 |
|
|
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' ENDIF @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'ENDIF @Color <> 'Red' AND @Flavor <> 'Sweet'BEGIN SELECT * FROM tblNotRedStuff Select @Flavor = 'OK'END |
|
|
thekeane
Starting Member
7 Posts |
Posted - 2005-08-03 : 12:09:46
|
Anyone??? |
|
|
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 selectset rs = cn.execute(sql) |
|
|
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 aboveMadhivananFailing to plan is Planning to fail |
|
|
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'ENDEXECUTE (@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?AjHey, it compiles. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 10:20:12
|
I don't seeSELECT * FROM tblAnyFlavorgetting 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_RedSweetStuffENDIF @Color = 'Red' AND @Flavor <> 'Sweet'BEGIN EXEC dbo.usp_RedStuffThatIsntSweetEND IF @Color <> 'Red' AND @Flavor = 'Sweet'BEGIN EXEC dbo.usp_NotRedSweetStuffENDIF @Color <> 'Red' AND @Flavor <> 'Sweet'BEGIN EXEC dbo.usp_NotRedStuffEND Kristen |
|
|
|
|
|