SQL Server - 异步查询执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8478691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Asynchronous Query Execution
提问by Ahmet Altun
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.
在 Sql Server 2008 中，我有一个存储过程，它将结果写入输出参数并将参数插入表中。我想让 SP 的“插入表”部分异步运行，以便可以从输出参数中读取结果，而无需等待插入命令完成。
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
回答by Remus Rusanu
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 differenttransaction). 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 thatissue.
为什么要从异步开始？插入的成本在响应延迟中通常是不明显的，除非它阻塞在锁上。如果您有锁定争用，请解决该问题。
回答by gbn
You can't do it in standard SQL: it is synchronous. You also can't process output parameters until the stored procedure has completed.
您不能在标准 SQL 中执行此操作：它是同步的。在存储过程完成之前，您也无法处理输出参数。
You'd have to use service brokerto decouple the calculation and the INSERT (Edit: As per Remus' answer with example)
您必须使用服务代理来解耦计算和 INSERT（编辑：根据 Remus 的示例回答）
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.
当然请注意，您现在需要更复杂的错误处理来处理任何错误和回滚，因为您的 INSERT 将被解耦并且您不会立即获得任何错误的反馈。
Of course, why not do the calculation in the client first? a.k.a what is the problem you're really trying to solve...
当然，为什么不先在客户端进行计算呢？也就是你真正想要解决的问题是什么......
