SQL Server - 异步查询执行 [英] SQL Server - Asynchronous Query Execution

问题描述

在 Sql Server 2008 中，我有一个存储过程，它将结果写入输出参数并将参数插入表中.我想让 SP 的插入表"部分异步运行，以便可以从输出参数中读取结果，而无需等待插入命令完成.

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.

我该怎么做?

例如.

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

推荐答案

这是可能的(参见 异步过程执行)，但结果很可能不是您想要的.首先也是最重要的异步意味着打破过程调用者假定的事务上下文(插入发生在不同事务中).此外，执行可靠的异步(如我链接的文章中所述)意味着执行更多的写入，因此没有性能优势.

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.

这篇关于SQL Server - 异步查询执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！