if.. else if.. else performance issues

Question

if.. else if.. else performance issues

ego1026

SSC Veteran

Points: 299
More actions
September 5, 2008 at 11:05 am

#96391

I have a sproc which contains an if, else if, else if, else flow. The sproc takes three input parameters and the conditional statements just check those variables in different combinations on whether they are null or not. When I run the sproc with values that make the first if statement true, it takes 11 seconds to run. However when I run only the query inside of the if statement (not the whole sproc) it runs in 1 second. The syntax in the sproc is correct; I have begins and ends in each of the if.. else if.. else conditions and tried putting print statements in each to make sure that it was only going into the correct conditional statement, which it was. Any ideas on why there would be such a huge difference in time?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Try running the whole script (copy out of the stored proc) with the "set statistics time on" statement prior to the first line of the code. My guess is that when you are looking at the whole thing there is a greater compile time than when you are just running the one section of code.

Let me know what you find.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

This smells like parameter-sniffing.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Possibly parameter sniffing. When the optimiser compiles the proc, it will optimise the queries based on the current parameter values, and it will optimise all queries in the proc, regardless of the if-else flow. If that plan is then reused and the parameters are different, you may get a very, very bad exec plan.

Consider the following example

CREATE PROC SearchSomething

@Code VARCHAR(10) = NULL

AS

IF @ID is null -- assume parameter wasn't passed

SELECT * FROM sometable

ELSE

SELECT * FROM SomeTable WHERE StatusCode = @Code

GO

Now, further assume that the first execution to that is with the parameter NULL. When the optimiser optimises the second select, it's going to evaluate aprox how many rows will satisfy the condition StatusCode = NULL. The answer to that is, of course, 0. Hence it will select a plan optimal for perhaps 1 row (perhaps an index seek and a bookmark lookup). The second select won't be executed in this case, but it still gets compiled and optimised

Now someone comes along later and executes that with a parameter 'A' and the second query will now return 5000 rows. That plan with the bookmark lookup is probably not so optimal anymore, but that's the plan that's there and it's what will be used.

What I usually suggest in this kind of case is a construct like the following

IF Condition1

EXEC subproc1

ELSE

IF Condition2

EXEC subproc2

...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ego1026 SSC Veteran Points: 299 More actions · Answer 4

thanks for the replies everyone I'll give these a try and let you know what I find

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

I have found that the last suggestion that Gail put forward with the use of subprocs works pretty well. Makes for a bit more work drilling down to the final procedure when you have to troubleshoot but the savings can sometimes be pretty nice.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ego1026 SSC Veteran Points: 299 More actions · Answer 6

DavidB: I ran the script with the execution times and got the following:

SQL Server parse and compile time:

CPU time = 11672 ms, elapsed time = 11965 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(460 row(s) affected)

SQL Server Execution Times:

CPU time = 219 ms, elapsed time = 635 ms.

Gail: I tried your approach and still got the same results

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Curious, what does the query in the sproc look like?

GSquared SSC Guru Points: 260824 More actions · Answer 8

Your compile time is killing it! Wow!

Definitely try the sub-procs route on this one. I use that all the time and it is pretty efficient.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Wow. 12 sec to compile...

If this server heavily used? Is that proc getting heavily used?

Can you run it again and trace (filtered by your machine name) the events Cache Hit, cache miss, cache insert and cache remove? I'd be interested to see how many occur and where the delays are.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

if.. else if.. else performance issues

Cookies on SQLServerCentral