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.
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.
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.