Some more context could help but if you call a single SP and pass some kind of switch to tell what should be done, it will be likely less clear and more error prone than just calling distincts SP.
Ie rather than
EXEC MyUniversalSP "DoA", etc..
EXEC MyUniversalSP "DoB", etc...
EXEC MyUniversalSP "DoC", etc... (and what if in some cases you don't need or want parameters etc..)
It seems easier to just have :
EXEC DoA
EXEC DoB
EXEC DoC (and each one will just have the parameters it really needs)
Plus when you edit a SP you'll see only what you care about for the particular case you are in rather than having tons of unrelated lines.
Would you create a huge C# method that would do unrelated stuff based on a parameter or would just create them as distinct methods?
As always ultimately your personal preference comes in play but mine should be quite clear ;-)
I don't think there could be an answer which is always correct. You should measure the efficiency by yourself. Use profiler, see execution plan, measure time.
If you google you could find pro and contra for every solution
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.
Splitting it into multiple sub-procs is generally a good idea, in terms of execution plans for each one. I've gotten very nice performance improvements by doing exactly that.
Member
15 Points
100 Posts
Single SP or Several SP's?
Oct 07, 2014 12:34 PM|sanjshah|LINK
Hi,
Is it more efficient to have one long sp containing several if statements or several smaller ones?
Thanks
All-Star
48570 Points
18082 Posts
Re: Single SP or Several SP's?
Oct 07, 2014 01:09 PM|PatriceSc|LINK
Hi,
Some more context could help but if you call a single SP and pass some kind of switch to tell what should be done, it will be likely less clear and more error prone than just calling distincts SP.
Ie rather than
EXEC MyUniversalSP "DoA", etc..
EXEC MyUniversalSP "DoB", etc...
EXEC MyUniversalSP "DoC", etc... (and what if in some cases you don't need or want parameters etc..)
It seems easier to just have :
EXEC DoA
EXEC DoB
EXEC DoC (and each one will just have the parameters it really needs)
Plus when you edit a SP you'll see only what you care about for the particular case you are in rather than having tons of unrelated lines.
Would you create a huge C# method that would do unrelated stuff based on a parameter or would just create them as distinct methods?
As always ultimately your personal preference comes in play but mine should be quite clear ;-)
All-Star
35149 Points
9075 Posts
Re: Single SP or Several SP's?
Oct 07, 2014 02:38 PM|smirnov|LINK
I don't think there could be an answer which is always correct. You should measure the efficiency by yourself. Use profiler, see execution plan, measure time.
If you google you could find pro and contra for every solution
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.
http://sqlmag.com/t-sql/if-statements-and-stored-procedure-performance
or
Splitting it into multiple sub-procs is generally a good idea, in terms of execution plans for each one. I've gotten very nice performance improvements by doing exactly that.
http://www.sqlservercentral.com/Forums/Topic611100-360-1.aspx
In other words, it depends.
I would suggest, keep all code in one SP until you feel comfortable with that and split it if it becomes unmanageable.