Submitting A Stored Procedure Asynchronously

Friday Oct 29th 2004 by Gregory A. Larsen
Share:

Greg Larsen discusses why you might want to run an SP asynchronously and how to submit an SP to run asynchronously.

Have you ever wondered if you could start the execution of a stored procedure (SP) asynchronously from some T-SQL code? What I mean is starting the SP in the background and continuing on with the next line of code in your T-SQL script without waiting for the execution of the SP to complete. If so, then this article might be of some interest to you. In this article I will discuss why you might want to run an SP asynchronously, how to submit an SP to run asynchronously, and lastly I have an example so you can test out running a stored procedure asynchronously.

Situation where Asynchronous Logic Will Help

Prior to showing you how to process an SP asynchronously, let me discuss a situation where asynchronous logic might enhance an application. Say I have an online order entry application, where operators take orders over the phone. My application went into production 12 months ago, and now my database has grown to over a million records. When this application first went into production, it took around 2 minutes to process an order, but as time went on the processing of orders starting taking longer and longer. Now that I have 1 million plus records in my database, a new order is taking upwards to 15 to 20 minutes to process. This performance slowdown has to do with a faulty database design. This flaw is causing loss of revenue, since customers are unwilling to wait on the phone for 20 minutes to process an order.

After some analysis, I realized the bulk of the wait time was associated with the convoluted design of my database and the slowness of updating all the tables that needed to be updated after the actual order was entered. Basically only a couple of tables need to be updated to complete the phone transaction, and the rest of the updates (the ones that take the bulk of 20 minutes or so) could be done after the customer is no longer on the phone. Therefore, I decided a simple fix was to take the original order entry SP and re-write it to submit the 20-minute transaction asynchronously. I chose this approach because that single order entry SP is called from lots of different places in the application, and changing the original processing logic would require a great deal of code re-write beyond just changing the single order entry SP.

Now that I know the problem with my SP, let me review the current poorly performing SP. I abbreviated the code below with comments to help minimize the code that needs to be reviewed:

create proc usp_enter_order (@custname char(40), @productid int, @quantity int)
as
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
 
-- Bulk of the code that runs slow
update inventory 
  set quantity = quantity - @quantity
 where productid = @productid 
.
.
[ Rest of all the slow code goes here ]
.
.

-- end of all the slow code  

By reviewing this code, you can see that at the top there is an "Initial Code" section. This code needs to be run while the customer is on the phone to verify that the order can actually be placed and entered into the database. Following the "Initial Code", you will find the "Bulk of the code that runs slow" section. This slow code is the T-SQL that I want to run asynchronously. By running this slow code asynchronously, the "Initial Code" will run, and then will submit the slow code to run in the background. This will allow the "usp_enter_order" SP complete, without having to wait the 20 minutes or so for the slow code to complete.



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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved