locked
multi-threading in t-sql RRS feed

  • Question

  • Hi SQL Gurus,

    I have a requirement to do multi threading on calling a stored procedure. Please advise with all possible solution.

    Current Infra-:

    Exec SP using a SQL Agent job -:

    --Get the list of Site id, Say 1500
    --Loop through each site id
    --Call each site id and call 2 SSRS report using rs.exe and generate 2 spread sheet.
    --Process ends.
    --Each site take 30 seconds to generate the spread sheet
    --which equates to 12 hour window for the entire process

    In order to cut down the time, I am thinking the following way, please review is this optimistic

    Create 4 SQL Agent Job (considering 4 threads)

    --Get the list of Site id, From 1 to 375
    --Loop through each site id
    --Ensure there are nolocks in all the sp calls, because everything is select only
    --Call each site id and call 2 SSRS report using rs.exe and generate 2 spread sheet.
    --Process ends.
    --Each site take 30 seconds to generate the spread sheet
    --which equates to 3 hour window for one subset

    --Get the list of Site id, From 376  to 750
    --Get the list of Site id, From 751  to 1125
    --Get the list of Site id, From 1125 to 1500

    Thanks,
    Ganesh



    Ganesh

    Monday, January 20, 2014 3:25 AM

Answers

All replies