Single SP or Several SP's?

Question

User801447716 posted

Hi,

Is it more efficient to have one long sp containing several if statements or several smaller ones?

Thanks

Answer 1

User753101303 posted

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 ;-)

Answer 2

User1508394307 posted

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.