SQL Server - Asynchronous Query Execution

Question

In Sql Server 2008, I have a stored proc that writes the result into an output parameter and that inserts the parameters into a table. I want to make the "inserting into a table" part of the SP to run asynchronously so that the result can be read from the output parameter without waiting the insert command complete.

How can I do it?

For example.

CREATE PROCEDURE dbo.Sample_sp
    @RESULT INT OUTPUT
    @PARAM_1 INT,
    @PARAM_2 INT,
    @PARAM_N FLOAT
AS

-- Perform Calculations like @RES = @PARAM_1 + @PARAM_2......
INSERT INTO DBO.A VALUES(@PARAM_1, @PARAM_2, ..... @PARAM_N)

 

EXECUTE ASYNC dbo.Sample_sp

Solution 1

It is possible (see Asynchronous procedure execution), but very likely the results will not be what you want. First and foremost going async implies breaking the transactional context assumed by the caller of the procedure (the insert occurs in a different transaction). Also, doing a reliable async (as in my linked article ) implies doing significantly more writes, so there is no performance benefit.

Why do you want to go async to start with? The cost of an insert is usually not noticeable in the response latency unless it blocks on locks. If you have locking contention, address that issue.

Solution 2

You can't do it in standard SQL: it is synchronous. You also can't process output parameters until the stored procedure has completed.

You'd have to use service broker to decouple the calculation and the INSERT (Edit: As per Remus' answer with example)

Note of course that you now need more complex error handling to deal with any errors and rollbacks because your INSERT would be decoupled and you don't get immediate feedback of any error.

Of course, why not do the calculation in the client first? a.k.a what is the problem you're really trying to solve...

Comments

[mistertodd]

The reason to go async is to go and do some separate operation that is unrelated to the atomic operation that the user perform. E.g., you add an answer on stack overflow, and we want to start the stored procedure that recalcalcualtes your reputation, your post history, your answer history, your activity history, the recent activity list. These things will take a while; but there is no reason you need to wait to 1-3 minutes it takes to perform all these calculations. The ugly workaround is to have an agent task run every 1 seconds, a scheduled task run every 1 seconds, or install service broker

[mistertodd]

why not do the calculation in the client first? A trigger that recalculates everything in the system based on your change takes too long; and there's no reason a user should wait for it. Other systems use service brokers, scheduled tasks, or agent jobs. But it would be nice to have it done in SQL Server directy. Rather than a trigger calling EXECUTE UpdateFincenCacheValues it can call EXECUTE ASYNC UpdateFincenCacheValues