DOWNLOAD THE CODE:
Download the Code 44717.zip

I have a stored procedure that executes one of two queries based on a value passed into it. Will I get better performance if I break the procedure into separate procedures? As I understand it, SQL Server determines a query execution plan based on the values specified the first time the query is run, so splitting up the stored procedure might help performance. But I'm not sure whether this behavior pertains to IF statements. Listing 1 shows an example of the current procedure, and Listing 2 shows an example of breaking the code into three separate procedures.

If SQL Server selects different query plans based on the different branches in the stored procedure, you're better off splitting the queries into separate stored procedures. Because SQL Server allows only one execution plan per stored procedure, leaving the queries in the same procedure will lead to stored procedure recompilation, which can reduce concurrency and increase CPU utilization.

For example, if you have multiple concurrent sessions executing the same stored procedure, your goal is to use the same execution plan. However, if the procedure's different branches cause SQL Server to recompile the execution plan, the system will have to synchronize between the sessions trying to get access to the compiled plan. Repeated recompilation of a stored procedure's execution plan is CPU intensive, so you'll also see increased CPU utilization.

The downside of breaking the queries into separate stored procedures is that you'll require more memory for the procedure cache because you'll need to hold extra plans in memory—in this case, three instead of one. To figure out how much memory the extra query plans will need, you can query the syscacheobjects system table in the master database. The result's sql column contains the query, and the sqlbytes column contains the size of the plan entry. The usecounts column shows whether or not you have plan reuse.

—Gert Drapers
Software Architect,
SQL Server Development Team

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Good because it explains to some extent how execution plan is used and the pro's and con's of the options available.

ashmiles- March 03, 2005

Article Rating 5 out of 5

At times I get quite complex SELECT statements, where I feel tempted to put in a big IF statement, instead of using repeated CASE statements, eg

SELECT col1, CASE WHEN (col2 = @param1) THEN col3 ELSE col4 END AS 'baa', CASE WHEN (col2 = @param1) THEN col5 ELSE col6 END AS 'boo', CASE WHEN (col2 = @param1) THEN col7 ELSE col8 END AS 'buu', col9 FROM table

What is the performance in such a scenario?

Also, I had recently a complex 4 times nested case statement and decided to create a new UDF for this and then call it in my procedure's SELECT statement. Is this good or bad?

borki- January 18, 2007

Article Rating 5 out of 5

I found the explanation interesting but it got me wondering about other 'IF' statements. Will a stored procedure need to be recompiled any time an 'IF' statement is present in a stored procedure and the branch of the IF statement executed changes based on the IF criteria. In other words, if an IF statement is just setting a value based on another value (i.e. IF @a = 1 then set @b=1 else set @b=0), will the stored procedure still need to be recompiled whenever the value in @a is changed or does a stored procedure only need recompiled if the branches off of the IF statement executes a query?

Blaise- January 18, 2007

Article Rating 5 out of 5

great information

bulentgucuk- January 19, 2007

Article Rating 5 out of 5

 
 

ADS BY GOOGLE