Post reply

parallel execution of a stored proc?

  • July 1, 2011 at 4:52 am

    #252939

    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

    #1347219

    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

    #1347228

    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

    #1347245

    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.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 4, 2011 at 2:07 am

    #1347659

    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

    #1347833

    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

    #1348357

    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

    #1348376

    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

    #1348788

    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

    #1349899

    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