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.