我建立一个应用程序，我想批处理多个查询到一个单一的往返到数据库。例如，让我们说一个单页需要显示的用户的列表，组的列表和权限的列表

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用户; EXEC getTeams; SELECT * FROM权限。

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

但我不知道是否有更好的方法来指定做3操作在一个往返。优点包括被更容易单元测试，并允许数据库引擎parrallelize查询。

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第二塔顶！我是认真的。很多时候我塞进一个小的额外的到我的数据库调用。没有pretty的。 您也可能会发现自己在一个情况下，你必须在某处配置您的SQL查询，并且你不能只让3来电：它必须是一个。它不应该是那个样子，不好的设计，但它是。你做你必须做的！ 有时候，当然它可以很好的封装存储过程中的多个步骤。通常不用于保存往返，虽然，但更严格的交易，获取ID为新的记录，限制其权限，提供封装，等等等等。 （But请不要开始使用存储过程的所有时间。） I once was limited to a crummy ODBC driver against a mainframe, and there was a 1.2 second overhead on each call! I'm serious. There were times when I crammed a little extra into my db calls. Not pretty. You also might find yourself in a situation where you have to configure your sql queries somewhere, and you can't just make 3 calls: it has to be one. It shouldn't be that way, bad design, but it is. You do what you gotta do! Sometimes of course it can be very good to encapsulate multiple steps in a stored procedure. Usually not for saving round trips though, but for tighter transactions, getting ID for new records, constraining for permissions, providing encapsulation, blah blah blah. (But please don't start using stored procedures all the time.)