Asynchronous Execution/Multithreaded

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

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

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

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

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

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

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.

Please let me know if you need more details or an example on this.

The other option is to look at using MSMQ. You should be able to call that from within SQL Server using the sp_OA* procedures...

seehttp://www.tagconsulting.com/Show.asp?Id=1000 for a possible solution

Cheers
Twan

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

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

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

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

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