How to Execute SQL Script Asynchronously

SQL code generally runs sequentially.  One line of code must complete before the next line is executed.  We can redirect the path that is followed through the code using conditional logic or GOTO statements, but ultimately we cannot progress to the next step until the previous one has completed.

However, there are situations where we may want the code to continue to step 2 without waiting for a particularly time-consuming step 1 to complete.  If step 1 and step 2 do not impact each other then it might be beneficial to have the SQL execute asynchronously or concurrently, saving overall execution time.

For an example let's create a table to insert data into for step 1, and a stored procedure that will insert data into it:

CREATE TABLE [dbo].[InsertSomething](
       id                   INT IDENTITY(1,1) NOT NULL,
       Col1                 VARCHAR(32) NULL,
       InsertedDate         DATETIME NULL
)
GO

CREATE PROC DoSomething @Data VARCHAR(32) = ''
AS
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - Start', GETDATE())
WAITFOR DELAY '00:00:10:00'
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - End', GETDATE())
GO

The stored proc DoSomething will insert two rows into the table InsertSomething.  After the first row is inserted there will be a delay of ten seconds before the second row is inserted.  Therefore the time for the procedure to complete is approximately ten seconds, which in this example represents a long running piece of code.

EXEC DoSomething 'Test 1'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2', GETDATE())
SELECT * FROM InsertSomething

Results

id          Col1                             InsertedDate

----------- -------------------------------- -----------------------

1           Test 1 - Start                   2014-12-30 12:38:40.023

2           Test 1 - End                     2014-12-30 12:38:50.030

3           Test 2                           2014-12-30 12:38:50.037

We can see from the timestamps that the "Test 2" record was inserted only after the stored procedure had completed inserting the Test 1 records. IE the sql script is executing sequentially, as expected.

In the above example we would prefer the "Test 2" insert statement not to have to wait for the stored procedure to complete. We would like the two sql commands to run concurrently.  To do this we need to create a second stored procedure to call the first via a new thread:

CREATE PROC [dbo].[AsyncInsert] @Data    VARCHAR(32)
AS

DECLARE @rc          INT
DECLARE @object      INT
DECLARE @osql_cmd    VARCHAR(1000)
DECLARE @ServerName  VARCHAR(256) = (SELECT @@SERVERNAME)

DECLARE @ExecStmt    VARCHAR(MAX) = '"EXEC DoSomething @Data='''+@Data + '''"'

EXEC @rc = sp_oacreate 'wscript.shell', @object OUT

SET @osql_cmd = 'osql -E -dMyDB -S' +@ServerName+' -Q'+ @ExecStmt

EXEC @rc=sp_oamethod @object, 'run', null, @osql_cmd

EXEC sp_oadestroy @object

GO  

Test the new procedure by running the following code:

EXEC [AsyncInsert] @Data = 'Test 1 Async'

WAITFOR DELAY '00:00:01:00'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2 Async', GETDATE())

WAITFOR DELAY '00:00:10:00'

SELECT * FROM InsertSomething

Results
id          Col1                             InsertedDate
----------- -------------------------------- -----------------------
1           Test 1 - Start                   2014-12-30 12:38:40.023
2           Test 1 - End                     2014-12-30 12:38:50.030
3           Test 2                           2014-12-30 12:38:50.037
4           Test 1 Async - Start             2014-12-30 12:39:01.967
5           Test 2 Async                     2014-12-30 12:39:02.930
6           Test 1 Async - End               2014-12-30 12:39:11.987

In the results above we can see that for the Async tests the timestamps show that the Test 2 insert  occurred while the stored procedure (Test 1) was still running  - ie after the "Test 1 Async - Start" insert command completed but before the "Test 1 Async - End" insert was executed.

We have generated a new thread to execute the stored procedure that does not require us to wait for it to complete before continuing.  Therefore the SQL script is now effectively running asynchronously.