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

Remus Rusanu · Accepted Answer · 2011-12-12 18:11:09Z

11

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.

answered Dec 12, 2011 at 18:11

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Boyd Over a year ago

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

gbn · Accepted Answer · 2011-12-12 18:15:33Z

10

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

edited Dec 12, 2011 at 18:15

answered Dec 12, 2011 at 18:09

gbn

434k84 gold badges602 silver badges690 bronze badges

1 Comment

Ian Boyd Over a year ago

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

R.Alonso · Accepted Answer · 2023-01-16 16:42:44Z

0

create a execute jobs

sp_start_job (Transact-SQL)

answered Jan 16, 2023 at 16:42

R.Alonso

1,0751 gold badge9 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server - Asynchronous Query Execution

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related