SQL Server Performance

Asynchronous Execution/Multithreaded

Discussion in 'Performance Tuning for DBAs' started by joshroth, Sep 9, 2003.

  1. joshroth New Member

    Is there a way to continue processing items in a stored procedure instead of waiting for the return of a previously embedded procedure call?

    For example:

    Stored Procedure A

    BEGIN
    EXECUTE PROC_B

    IF X = Y
    BEGIN
    --Do Something Here
    END
    END

    Basically, I don't care what happens in PROC_B, I just want to spawn the process off and continue with the rest of the code.

    Any help would be greatly appreciated.

    Joshua Roth
    jroth@joshroth.com
  2. gaurav_bindlish New Member

    SQL Server does not provide the functionality of asynchronous execution from SQL Statements. However, as a work around, you can trigger a job which will execute PROC_B. In this case, the control of the statement will come back to the calling stored procedure as soon as the job is kicked off and you don't have to wait for the PROC_B to end.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. joshroth New Member

    Thanks.

    After I wrote the post I decided to write it as a Trigger, so it's good to know I am in synch with you.

    They should add that ability to future releases of SQL Server. Something like EXECUTE with a parameter to wait or not wait, and then it could spawn it's own thread.

    Joshua Roth
    jroth@joshroth.com
  4. ykchakri New Member

    I think what Gaurav meant is not a SQL Trigger, but a SQL Scheduler Job that you create and start (trigger) from your parent stored procedure.

    For Example:

    Stored Procedure A

    BEGIN
    exec msdb..sp_add_job @job_name = 'exec ProcB'
    exec msdb..sp_add_jobstep @job_name = 'exec ProcB',@step_name = 'exec ProcB', @command = 'EXECUTE PROC_B', @flags = 4
    exec msdb..sp_start_job @job_name = 'exec ProcB'

    IF X = Y
    BEGIN
    --Do Something Here
    END
    END
  5. joshroth New Member

    I see. I think that will work great. I am going to actually use a trigger to start the job. Should do exactly what I need.

    Thanks again, you guys kick ass!

    Joshua Roth
    jroth@joshroth.com
  6. joshroth New Member

    One slight problem with this spawning of a job...you can only run 1 instance of the job at a time. So, if the job takes a while for an item and I'm ready to spawn it again, it won't.

    Any other thoughts or things I'm missing?

    Joshua Roth
    jroth@joshroth.com
  7. ykchakri New Member

    You can do this by implementing a bit of dynamic sql in to your create job statement. Instead of a static job name you can append a variable to the job name and you can make this variable to hold a different item name/number for each spawn.
  8. ykchakri New Member

    Please let me know if you need more details or an example on this.
  9. Twan New Member

  10. Twan New Member

    Actually thinking about it overnight.

    You could just insert a row of parameters into a table and have a job that polls that table to process what's in there...?

    What is it that you are trying to do asynchronously?

    Cheers
    Twan
  11. joshroth New Member

    That is basically what I was doing before.

    I have an application that inserts some data into a table once a user presses some buttons on an IVR application. The table that it gets inserted into is then polled every minute for items that need further processing. If an item is found in the table more processing is done (which can be very time intensive). The problem is that I can't do the full processing required while the user is on the IVR in that the wait time or dead space while sitting on the phone is a problem. The reason that the polling doesn't work is that it will only process one item in the table at a time. They all need to have equal priority, and therefore they all need to be processed asynchronsouly.

    The solution I finished yesterday is to create the dynamic job on insert with a trigger. The jobs each execute the appropriate stored procedure in Step 1. In Step 2 I have the job delete itself. It seems to work pretty well, but obviously I need to do some further testing on it.

    Thanks.

    Joshua Roth
    jroth@joshroth.com
  12. Twan New Member


    Can't the job skip over any rows that can't yet be processed either by the application setting a flag when the IVR is finished with this row or by the procedure moving onto the next row when it can't process the row...?

    If the processing is very time intensive, then is that not a bigger reason for processing the rows asynchronously but serially? What kind of processing does it need to do? No risk of locking, over stressing the hardware, etc?

    Cheers
    Twan
  13. joshroth New Member

    Once the rows are inserted, they can be processed. There is no reason not to process them. So skipping won't make sense.

    When I say time intensive, it's from a user prospective. I can't have the users sitting on the phone with dead air waiting to process. Could I make the process faster, make it less time intensive, make it faster, etc.? I'm sure I could, but I'm not a dedicated DBA, we don't have one, and thus we are stuck with my expertise...

    That being said, I think the process is pretty succinct, it just has a lot to do, but that's another topic for another thread...

    Joshua Roth
    jroth@joshroth.com
  14. Twan New Member

    <br />Good luck with it Joshua <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan

Share This Page