April 26, 2021 at 8:26 pm
Evening Guys,
Are there negative effects of having multiple stored procs wrapped in an outer/master proc? Will the inner procs that are executed all be run as one giant transaction?
Some folks here like to do it this way so they only have to schedule one proc. Which make sense from that point of view. Though I find debugging it a bit of a hassle. But I am not clear on the effects on performance. These are big procs too.. each proc deals with a few hundred million records and they're fairly deep execution plans.
I wanto break them out into individual steps. Is there a reason (other than diffculity of creating jobs in future) not to do this?
Cheers All,
Alex
April 27, 2021 at 2:16 am
It's actually another way around.
Bigger code structured into many nested stored procedures containing smaller chunks of the code will almost certainly give better performance.
Beginning with the compilation stage: long code is long to compile, to many options to consider when choosing the execution plan, there is a good chance optimiser won't get to the best plan and you end up with less than optimal performance.
With smaller chunks of code compilation process for each of them will be faster, optimiser will most certainly get to the optimal plans, and in most cases recompilation won't be necessary for majority of those stored procedures.
The argument about transactions is absolute BS. Stores procedures don't imply transactions. If outer code does not open a transaction then no piece of code will be in a transaction.
It just shows that those "some folks" have very little knowledge about how SQL Server works and you should not really listen to their advice.
_____________
Code for TallyGenerator
April 27, 2021 at 6:07 am
Evening Guys,
(1) Are there negative effects of having multiple stored procs wrapped in an outer/master proc?
(2) Will the inner procs that are executed all be run as one giant transaction?
(3) Some folks here like to do it this way so they only have to schedule one proc. Which make sense from that point of view. Though I find debugging it a bit of a hassle. But I am not clear on the effects on performance. These are big procs too.. each proc deals with a few hundred million records and they're fairly deep execution plans.
(4) I want to break them out into individual steps. Is there a reason (other than diffculity of creating jobs in future) not to do this?
Cheers All, Alex
I added q/topic numbers so people can respond to multiple qs without having to repeat the text (hint, hint! 🙂 ).
(1) There can be. One big hassle is if you pass a lot of params to the first proc, it can be a pain to have to pass them to child procs. But I think it's worth it. I say avoid "monster" procs if you can.
(2) You control that. If you start a trans in the main/driver proc, then all procs you EXEC after that will be part of that same trans, until/unless you explicitly end the trans.
(3) You still only have to schedule one proc. That driver/"master" proc calls the other procs. One great thing is that temp tables created in the first proc can be used by any later called proc.
(4) I wouldn't think in terms of job steps. Stick to terms of procs, and always design the procs to meet the business needs. That is, don't cram two procs together just so a single EXEC in a job step can run both. It's better to just run two procs in one step. Or, if your shop insists on one proc per step only, then just use two steps to EXEC the two procs.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 27, 2021 at 6:12 am
n/a
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 28, 2021 at 8:20 am
Cheers Guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply