SQL Server Performance Forum – Threads Archive
Sproc performance
Is there any "big" difference on the performance of an sproc between one with multiple if-statments and one with none. Suppose I have this:–sproc1 create proc sproc1
@flagX bit,
@flagY bit
as
if @flagX=1
UPDATE tblX SET fld1=1 WHERE Id=5
else @flagY=1
UPDATE tblY SET fld1=1 WHERE Id=5
else
UPDATE tblY SET fld1=1, fld2=2 WHERE Id=5 –some codes goes here And if i write this stored proc separating each update statement on each if-else and copying the "some codes" on each stored proc, I will then have three sproc, will it perform better? Thanks in advance!
Typically (maybe not in this exact case), a stored proc will undergo a recompilation if it has several different execution paths acting on different objects. Yours potentially updates tblX or tblY depeding on the param, so Im guessing it would recompile. You could check this by doing a profiler trace and monitoring recompilations. The performance boost to be had here, is to enure that cached execution plans have a long lifetime, and the proc recompiles as infrequently as possible. This would definitely be achieved by splitting the various paths out into discrete stored procs. As always with SQL though, its best to run some tests and create a valid proof case before making a change.
Hi ya, the single stored proc will take longer to compile since it has more code in it, but it will not recompile when called with different parameters. The plan is worked out based on the first parameters passed to it. splitting the proc into multiple procs might make sense if
– the code is more complicated then what you’ve shown above
– the code typically runs through one path 80-90% of the time, the other paths are rarely used
– the code has between statements where different sized ranges can be passed into the prco which might benefit from different execution plans if your proc is basically what you’ve got above, then I’d suggest grabbing a generated proc from a replicated database and look at the update proc. This uses a bitmask to work out which columns are to be updated, and is still efficient… (I haven’t got a subscriber database here to pull out a MS generated update type proc Cheers
Twan
Hi thanks for the replies! If I break it up to 3 sprocs would there be any difference then on the performance since each path will call the 3rd sproc containing the "some codes". I’ve used both STATISTICS TIME and STATISTICS IO to test the performance hoping to see if there is any difference but there was none (I might be using the wrong method to test). Twan,
I guess based on your criteria my sproc should stay the same. But taking scaleability into consideration do you think it’s better to split it since one path on the beginning of the code has another (similar path) that executes more code after the "some codes". In this manner I have the "some codes" encapsulated in an sproc, and can be called by other (new) sproc that should have been another (new) path on the original sproc. Thanks
Hi ya, the way your code is written above the ‘some codes’ bit would get executed for every call to the original proc, since there is no being-end block to ensure that it belongs only with the last else In terms of performance difference I haven’t ever been able to detect any marked difference from splitting a proc for the sake of it unless either
– the proc takes in values which substantially change the optimum plan for any of the sql statements in the proc
– the proc has a lot of code which is only required in a very small percentage of executions Cheers
Twan
Hi Twan, Yes the "some codes" is outside the if-expression. I take it that splitting the sproc is ok performance wise. How about considering the design/structure of the sproc, how would DBAs or DB developers normally create such sproc? Split or not? Yes, I know what you guys said that it depends if it’s complicated enough to merit it for breaking into smaller sprocs but what if, in my case, the logic is that this sproc is called by different methods. And @flagX and @flagY determines which path to use. I need to know your idea if you will be the ones who will do it. Thanks!
Hi ya, in the case shown above I would write it as a single proc, unless
:- the update statement itself was required from other callers without using the rest of the procedure logic
:- the project was large enough to warrant layering within the database proc design, i.e. have CRUD procedures for each table and ensure that these are always used by a layer for application accessible stored procedures in preference to updating tables directly Cheers
Twan
]]>