Last post Oct 07, 2014 02:38 PM by smirnov
Oct 07, 2014 12:34 PM|sanjshah|LINK
Is it more efficient to have one long sp containing several if statements or several smaller ones?
Oct 07, 2014 01:09 PM|PatriceSc|LINK
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 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 ;-)
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.
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.
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.