July 12, 2011 at 9:13 am
Currently, I have a scheduled stored procedure, which then executes numerous steps and about another 20 other smaller stored procedures in sequence. The whole process updates numerous tables from the live system database.
The system works, everything gets done in a logical order, but the the time from start to completion is more than I like.
I have analyzed all the steps, and I can actually run most of the small steps in parallel instead of in sequence, and should shave 1 to 2 hours of the whole procedure.
Now I know I can execute (call) another stored procedure from within the currently running procedure. This will however pause the current procedure until the called procedure is complete.
Can I call procedure to execute at the same time? So instead of running #1, then #2, then #3, is there a way to issue a command to start #1 AND #2 AND #3 right now?
My alternative is to break up the large procedure into 2 or 3 procedures and just schedule those three to run at scheduled times. But I would really prefer to only have one main procedure to schedule, which would then start the smaller steps in unison instead of in sequence.
Any suggestions?
July 12, 2011 at 9:27 am
may be You can think of doing it using an ssis package where parallel processing is possible. or you can think of scheduling a job which will in turn executes as many sp _ start _ job's.
July 12, 2011 at 9:50 am
I was gonna say multiple jobs as well, but I would push SSIS instead. You would have much greater control over the process and order if you do it in SSIS.
CEWII
July 12, 2011 at 1:53 pm
If you're ready for a new challenge asynchronous behavior can be achieved purely in T-SQL using Service Broker.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 2:08 pm
opc.three (7/12/2011)
If you're ready for a new challenge asynchronous behavior can be achieved purely in T-SQL using Service Broker.
I absolutely agree it can be done with service broker, but for this I think this might be a little too advanced. Just my $0.02..
CEWII
July 12, 2011 at 2:20 pm
I would recommend SSIS also - and I don't see Service Broker as the right solution. I would think service broker is more geared toward event driven processing - where some action (e.g. insert into a table) starts a process that the service broker takes care of.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2011 at 2:28 pm
Elliott Whitlow (7/12/2011)
opc.three (7/12/2011)
If you're ready for a new challenge asynchronous behavior can be achieved purely in T-SQL using Service Broker.I absolutely agree it can be done with service broker, but for this I think this might be a little too advanced. Just my $0.02..
CEWII
Maybe so. Service Broker is not covered in Chapter 1 of a general SQL Server text. The OP can make up their own mind.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 2:43 pm
Jeffrey Williams-493691 (7/12/2011)
I would recommend SSIS also - and I don't see Service Broker as the right solution. I would think service broker is more geared toward event driven processing - where some action (e.g. insert into a table) starts a process that the service broker takes care of.
I am quite surprised by this comment. The "event" would be the addition of a message to the SB queue. The "process that service broker takes care of" would be the execution of the stored proc that was capable of running in parallel with other stored procedures.
There are tons of examples online offering up Service Broker for this exact use-case. "right solution" is decidedly subjective here. The OP may not care to use it, but I have to respectfully disagree with your assertion, SB would be a fine way to solve the OP's problem.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 8:10 pm
opc.three (7/12/2011)
Jeffrey Williams-493691 (7/12/2011)
I would recommend SSIS also - and I don't see Service Broker as the right solution. I would think service broker is more geared toward event driven processing - where some action (e.g. insert into a table) starts a process that the service broker takes care of.I am quite surprised by this comment. The "event" would be the addition of a message to the SB queue. The "process that service broker takes care of" would be the execution of the stored proc that was capable of running in parallel with other stored procedures.
There are tons of examples online offering up Service Broker for this exact use-case. "right solution" is decidedly subjective here. The OP may not care to use it, but I have to respectfully disagree with your assertion, SB would be a fine way to solve the OP's problem.
I guess we will have to agree to disagree. 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2011 at 5:20 am
Agreed :-D!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply