July 1, 2011 at 4:52 am
Hello All,
I've got a stored proc (all 12500 lines of it!) that takes 7 minutes to run. I need to run it 13 times with different input parameters.
It is quite safe to run in parallel (ie open up 13 query editor panels and call from each) and all 13 runs will complete inside of 8 minutes.
If I schedule it, it will run all 13 batches in series, taking 91 minutes.
Is there any way I can create threads inside a t-sql batch so that it calls the sproc 13 times simultaneously? thus making the scheduled run take 8 minutes?
Cheers.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 1, 2011 at 11:18 am
Service Broker can assist you with getting asynchronous behavior.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 11:24 am
I *think* that if you just schedule 13 jobs, and have them each start at the same time, it will execute all jobs simultaneously, and have them run in parallel. But I'm not 100% certain on that - the times where I've used this approach, it could just have been that the jobs happened to be finishing at around the same time.
But yeah, the service broker is definitely the way to go if you want to do asynchronous execution of tasks in SQL Server
July 1, 2011 at 11:58 am
Just a warning: Depending on how the proc is written you may end up with heavy locking, blocking or even deadlocking if running the code in parallel. This may cause the overall execution time being much more than what you expect...
If there are any loops (either W.H.I.L.E. or c.u.r.s.o.r. *cough*) you might gain much more by improving the code than by calling it in parallel. It's a lot more complicated than run the code in parallel, I admitt. But the long term benefit might still be worth it the effort.
July 4, 2011 at 2:07 am
Cheers Guys.
I shall look into service broker.
Lut - Good advice ta, but I already have run this sproc in paralel - i just kick off 13 queries in management studio manually and it works fine.
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 4, 2011 at 7:18 am
Hi
Another way is to create a ssis /DTS package and call 8 execute sql task parallely and provide the parameter accordingly and run
July 5, 2011 at 9:23 am
Unless you are already a SSB proficient user, that is just not the best way to go here. Simply create 13 jobs and schedule them to run at same time. I note that you can create ONE of those jobs then script it out and simply modify the script in a few places to build the other 12. WAY easier and faster than creating them manually.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 5, 2011 at 9:41 am
I know you're capable Ben but if you have not used it and get shy about SSB one time saver I'll throw out there about Agent jobs: make one schedule and just attach it to all 12 jobs. No sense in having 12 separate schedules out there. Then if you need to change the timing you only need to change the one schedule instead of 12.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 2:04 am
Cheers guys.
I will have a play with service broker - I think it would be a useful skill to have anyway. I though about scheduling jobs, but I dont want 13 jobs in there! It currently looks nice and tidy with just 3 jobs listed...
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 7, 2011 at 11:42 am
BenWard (7/6/2011)
Cheers guys.I will have a play with service broker - I think it would be a useful skill to have anyway. I though about scheduling jobs, but I dont want 13 jobs in there! It currently looks nice and tidy with just 3 jobs listed...
As a consultant you know what I say when I hear about developers doing things like you are planning on because you like "nice and tidy" graphical stuff - KAAAACCCHHHHIIIINNNNGGGGG!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply