Submitting A Stored Procedure Asynchronously
October 29, 2004
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.