如何并行运行 sql server 存储过程？
我想做一些类似的事情：
exec sproc1 and sproc2 at the same time
when they are both finished exec sproc3
我可以在 dts 中做到这一点。 有没有办法在transact sql中做到这一点？ 或者有没有办法用批处理脚本（例如vbs或powershell）来做到这一点？
I want to do something like:
exec sproc1 and sproc2 at the same time
when they are both finished exec sproc3
I can do this in dts.
Is there a way to do it in transact sql?
Or is there a way to do it with a batch script (eg vbs or powershell)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以创建一个 CLR 存储过程（使用 C#）在其自己的线程上调用前两个过程，然后阻塞直到两个过程都完成...然后运行第三个过程。
您能够在您的情况下使用 CLR 存储过程吗？ 如果是这样，我将编辑此答案以获得更多详细信息。
You could create a CLR Stored Procedure that (using C#) would call the first two on their own threads, and then block until both are complete... then run the third one.
Are you able to use CLR sprocs in your situation? If so, I'll edit this answer to have more detail.
sp _ start _ job
我现在正在做类似的事情，我发现避免使用 SSIS 或某些外部 shell 的唯一方法是将我的加载例程手动拆分为“线程”，然后触发单个主程序sqlagent 作业反过来执行与我有线程一样多的 sp _ start _ job 。 从那时起，它们都自主运行。
这不完全是我们想要的，但结果是一样的。 如果您测试子作业的作业状态，您也可以实现存储过程 3 的条件启动。
如果我们不能同时使用 8 个核心，那么它们有什么意义呢？
sp _ start _ job
I'm doing a similar thing at the moment, and the only way I've found to avoid using SSIS or some external shell is to split my load routine into 'threads' manually, and then fire a single master sqlagent job which in turn executes as many sp _ start _ job's as I have threads. From that point, they all run autonomously.
It's not exactly what we're looking for, but the result is the same. If you test the job status for the sub jobs, you can implement your conditional start of sproc 3 as well.
What's the point in 8 cores if we can't use them all at once?
您绝对需要两个 SP 并行执行吗？
通过单个 SP 中的简单 CRUD 语句，我发现 SQL S. 在确定哪些语句可以并行运行方面做得非常好。 我从未见过 SQL S. 并行运行 2 个 SP（如果从 T-SQL 语句顺序调用这两个 SP），甚至不知道这是否可能。
那么，DTS真的并行执行它们吗？ 它可能只是按顺序执行它们，然后在最后一个 SP 成功完成后调用第三个 SP。
如果它真的并行运行它们，也许你应该坚持使用 DTS，但是我想知道如果我有一个 DTS 包调用，比如说并行 10 个重型 SP，它会做什么...我可能必须这样做一些测试来了解我自己：D
Do you absolutely need both SPs to be executed in parallel?
With simple CRUD statements within a single SP, I've found SQL S. does a very good job of determining which of them can be run in parallel and do so. I've never seen SQL S. run 2 SPs in parallel if both are called sequentially from a T-SQL statement, don't even know if it's even possible.
Now then, do the DTS really execute them in parallel? It could be it simply executes them sequentially, then calls the 3rd SP after the last finishes successfully.
If it really runs them in parallel, probably you should stick with DTS, but then I'd like to know what it does if I have a DTS package call, say, 10 heavy duty SPs in parallel... I may have to do some testings to learn that myself :D
您可以使用 SSIS。 这样做的好处是包可以存储在 SQL Server 中并在那里轻松调度。
从 PowerShell 或任何外部脚本语言中，您可以使用 SQL 命令行 osql 或 sqlcmd。 该技术也可用于通过使用 xp_cmdshell 进行 shell 处理来在 SQL Server 上安排它。
You can use SSIS. The benefits of this are that the package can be stored in the SQL Server and easily scheduled there.
From PowerShell or just about any external scripting language, you can use the SQL command line osql or sqlcmd. This technique can also be used to schedule it on the SQL Server by shelling out using xp_cmdshell also.