一次往返执行多个 SQL 命令 [英] Execute multiple SQL commands in one round trip

问题描述

我正在构建一个应用程序，我想将多个查询批处理到一个数据库的单次往返中.例如，假设单个页面需要显示用户列表、组列表和权限列表.

I am building an application and I want to batch multiple queries into a single round-trip to the database. For example, lets say a single page needs to display a list of users, a list of groups and a list of permissions.

所以我已经存储了过程(或者只是简单的 sql 命令，如从用户中选择 *")，我想执行其中的三个.但是，要填充这一页，我必须进行 3 次往返.

So I have stored procs (or just simple sql commands like "select * from Users"), and I want to execute three of them. However, to populate this one page I have to make 3 round trips.

现在我可以编写单个存储过程(getUsersTeamsAndPermissions")或执行单个 SQL 命令select * from Users;exec getTeams;select * from Permissions".

Now I could write a single stored proc ("getUsersTeamsAndPermissions") or execute a single SQL command "select * from Users;exec getTeams;select * from Permissions".

但我想知道是否有更好的方法来指定在一次往返中执行 3 次操作.好处包括更容易进行单元测试，并允许数据库引擎并行化查询.

But I was wondering if there was a better way to specify to do 3 operations in a single round trip. Benefits include being easier to unit test, and allowing the database engine to parrallelize the queries.

我正在使用 C# 3.5 和 SQL Server 2008.

I'm using C# 3.5 and SQL Server 2008.

推荐答案

你提到的单个多部分命令和存储过程选项是两个选项.您不能以使它们在数据库上并行化"的方式来执行它们.但是，这两个选项都会导致单次往返，所以你很好.没有办法更有效地发送它们.在 sql server 2005 以后，完全参数化的多部分命令非常有效.

The single multi-part command and the stored procedure options that you mention are the two options. You can't do them in such a way that they are "parallelized" on the db. However, both of those options does result in a single round trip, so you're good there. There's no way to send them more efficiently. In sql server 2005 onwards, a multi-part command that is fully parameterized is very efficient.

编辑:将有关为什么塞进一个电话的信息.

Edit: adding information on why cram into a single call.

虽然您不想太在意减少通话次数，但可能有这样做的正当理由.

Although you don't want to care too much about reducing calls, there can be legitimate reasons for this.

• 我曾经被限制在大型机上使用糟糕的 ODBC 驱动程序，每次调用都有 1.2 秒的开销！我是认真的.有时我会在我的数据库调用中塞进一点 extra.不漂亮.
• 您还可能会发现自己必须在某处配置 sql 查询，而且您不能只进行 3 次调用:它必须是一次.不应该那样，糟糕的设计，但它是.你做你该做的！
• 当然，将多个步骤封装在一个存储过程中当然会非常好.通常不是为了节省往返行程，而是为了更紧凑的交易、获取新记录的 ID、限制权限、提供封装等等.

这篇关于一次往返执行多个 SQL 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！