Submitting A Stored Procedure Asynchronously - Page 2

Changing Stored Procedure to Submit Code Asynchronously

Now that you understand the initial performance problem with SP "usp_enter_order," let me discuss how I could re-write this SP to submit the slow code asynchronously. First, I will need to create a "new" SP that contains the slow code. The second thing will be to replace the slow code in "usp_enter_order" with some OLE Automation that submits the "new" SP asynchronously. Below is the code for the new SP, I called it "usp_run_slow_code":

create proc usp_run_slow_code 
  (@productid int, @quantity int)
as
declare @currdate datetime
declare @orderid int

update inventory 
  set quantity = quantity - @quantity
 where productid = @productid 
-- rest of all the slow code goes here

-- end of all the slow code

As you can see, I just cut and pasted the original code from "usp_enter_order" into this new SP "usp_run_slow_code". Nothing else besides this needs to be done.

Here is the revised code for "usp_enter_order" that will submit "usp_run_slow_code" asynchronously, using OLE Automation:

create proc usp_enter_order (@custname char(40), @productid int, @quantity int)
as
set nocount on
declare @currdate datetime
declare @orderid int
declare @custid int
-- initial code to setup order that runs quick
insert into customer (custname) values (@custname)
select @custid = custid from customer where custname = @custname
set @currdate = getdate()
insert into orders (custid, orderdate,productid, quantity)
     values (@custid, @currdate,@productid, @quantity)
select @orderid=orderid from orders where custid = @custid and orderdate = @currdate
-- rest of initial code to setup order goes here

-- end of initial setup code

--submit usp_run_slow code asynchronously
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @osql_cmd varchar(1000)
-- create shell object 
exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc <> 0
begin
 exec sp_oageterrorinfo @object, @src out, @desc out 
 select hr=convert(varbinary(4),@rc), 
           source=@src,
           description=@desc
 return
end
set @osql_cmd = 'osql -E -dtest -Sserver1  -Q"usp_run_slow_code 1,1'
-- submit usp_run_slow_code
exec @rc=sp_oamethod @object,
                     'run',
                     null,
                     @osql_cmd

print @rc
if @rc <> 0
begin
 exec sp_oageterrorinfo @object, @src out, @desc out 
 select hr=convert(varbinary(4),@rc), 
           source=@src, 
           description=@desc
 return
end
-- destroy shell object
exec sp_oadestroy @object

If you review this code, you will see I have created a "wscript.shell" object using OLE automation SP "sp_oacreate". I then use the "run" method of this object to submit an "osql" command using the OLE automation SP "sp_oamethod". Using this object and method starts another Windows process to run the "osql" command, and returns to the calling routine without waiting for the other process to complete. The "osql" command runs a single T-SQL statement to execute my "usp_run_slow_code" SP. Submitting my SP this way allows me to run "usp_run_slow_code" SP asynchronously from "usp_enter_order" SP.

Rollback considerations

Since asynchronous code will be run as a different batch than the initial code, there could be transaction rollback considerations in separating code. Therefore, before you consider submitting any asynchronous code you will need to review your design constraints to make sure you do not cause data integrity issues by breaking up a single logical transaction into multiple transactions.

Simple Example to Test out Asynchronous Logic

My fictitious example above is not something you can really use to test out asynchronous logic. Therefore, I have developed the following simple example so you can test and verify submitting an SP asynchronous using OLE automation.

Below you will find an SP called "usp_async". This SP runs for 5 minutes. At the end of five minutes, it creates a table call "async". This SP simulates a long running SP by using the "WAITFOR DELAY" T-SQL command. Here is the code:

use test
go 
create proc usp_async
as 
waitfor delay '00:05:00'
create table async (a int)
go

To test out running "usp_async" asynchronously I have put together the following T-SQL code. This T-SQL script uses OLE Automation, just like my above example, to submit the "usp_async" SP asynchronously.

use test 
go 
set nocount on
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
print 'starting ....'
exec @rc = sp_oacreate 'wscript.shell', @object out
print @rc
if @rc <> 0
begin
 exec sp_oageterrorinfo @object, @src out, @desc out 
 select hr=convert(varbinary(4),@rc), 
           source=@src, 
           description=@desc
 return
end
print 'executing usp_async'
exec @rc=sp_oamethod @object,
                     'run',
                     null,
                     'osql -E -dtest -Sserver1  -q"usp_async"'
if @rc <> 0
begin
 exec sp_oageterrorinfo @object, @src out, @desc out 
 select hr=convert(varbinary(4),@rc), 
           source=@src, 
           description=@desc
 return
end
exec sp_oadestroy @object
print 'done....but usp_async still running'

To test out this simple example, first create the "usp_async" SP, then copy the code above into a Query Analyzer window, and run. Note I have created the "usp_async" SP in database "test". If you create the "usp_async" SP in another database, then you will need to modify the above SP and T-SQL code. After you have executed the above code in Query Analyzer, go right away into Enterprise Manager and determine if table "async" exists. It should not exist, unless you already have a table "async" prior to running this test. Keep refreshing the tables in your database to verify if table "async" exists. After 5 minutes of refreshing the list of tables in Enterprise Manager, you should see table "async" appear.

Conclusion

Not everyone or every application will require running code asynchronously. However, if you should have a needed to run code asynchronously then hopefully the above example has given you some ideas on how to accomplish this using OLE Automation.

» See All Articles by Columnist Gregory A. Larsen