在一次往返中执行多个SQL命令

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.

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

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.

所以我已经存储了procs(或者简单的sql命令，比如“select * from Users”)，我想执行其中的三个。然而，为了填充这一页，我必须进行3次往返。

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

现在，我可以编写一个存储的proc(“getUsersTeamsAndPermissions”)或执行一个SQL命令“select * from Users;exec getTeams;select * from Permissions”。

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.

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

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

我正在使用c# 3.5和SQL Server 2008。

6 个解决方案

#1

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.

您提到的多部分命令和存储过程选项是两个选项。你不能以这样一种方式去做它们在db上是“并行化”的。然而，这两种选择都会导致一次往返旅行，所以你做得很好。没有任何方法可以更有效地发送它们。在sql server 2005之上，一个完全参数化的多部分命令非常有效。

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.

尽管您不想过多地关注减少调用，但这可能有合理的原因。

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.
我曾经被限制在一个崩溃的ODBC驱动程序的主机上，并且在每个调用上有1.2秒的开销!我是认真的。有时我在我的数据库调用中塞入一些额外的东西。不漂亮。
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!
您可能还会发现自己不得不在某个地方配置sql查询，而且不能只进行3次调用:必须是1次。它不应该是那样的，糟糕的设计，但它是。你做你该做的!
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.)
当然，有时封装存储过程中的多个步骤是非常好的。通常不是为了节省往返行程，而是为了更紧凑的事务，为新的记录获取ID，限制权限，提供封装等等。(但请不要一直使用存储过程。)

#2

Something like this. The example is probably not very good as it doesn't properly dispose objects but you get the idea. Here's a cleaned up version:

是这样的。这个例子可能不是很好，因为它不能正确地处理对象，但是您可以理解。这里有一个干净的版本:

using (var connection = new SqlConnection(ConnectionString))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = "select id from test1; select id from test2";
    using (var reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                Console.WriteLine(reader.GetInt32(0));
            }
            Console.WriteLine("--next command--");
        } while (reader.NextResult());

    }
}

#3

Making one round-trip vs three will be more eficient indeed. The question is wether it is worth the trouble. The entire ADO.Net and C# 3.5 toolset and framework opposes what you try to do. TableAdapters, Linq2SQL, EF, all these like to deal with simple one-call==one-resultset semantics. So you may loose some serious productivity by trying to beat the Framework into submission.

一次往返要比三次更有效。问题是这样做是否值得。整个ADO。Net和c# 3.5工具集和框架反对你试图做的。TableAdapters、Linq2SQL、EF，所有这些都喜欢处理简单的one-call== =one-resultset语义。因此，您可能会通过尝试将框架提交来降低一些严重的生产力。

I would say that unless you have some serious measurements showing that you need to reduce the number of roundtrips, abstain. If you do end up requiring this, then use a stored procedure to at least give an API kind of semantics.

我想说的是，除非你有一些严肃的测量数据表明你需要减少往返次数，否则就戒掉。如果您最终需要这样做，那么使用存储过程至少提供某种API语义。

But if your query really is what you posted (ie. select all users, all teams and all permissions) then you obviosuly have much bigger fish to fry before reducing the round-trips... reduce the resultsets first.

但如果你的问题确实是你所发布的。选择所有的用户，所有的团队和所有的权限)然后你很可能在减少往返之前有更大的事情要做…首先减少结果集。

#4

I this this link might be helpful.

这个链接可能会有帮助。

Consider using at least the same connection-openning; according to what it says here, openning a connection is almost the top-leader of performance cost in Entity-Framework.

考虑至少使用相同的连接-打开;根据本文所述，在实体框架中，打开连接几乎是性能成本的最高领导者。

#5

Firstly, 3 round trips isn't really a big deal. If you were talking about 300 round trips then that would be another matter, but for just 3 round trips I would conderer this to definitley be a case of premature optimisation.

首先，三轮旅行并不是什么大不了的事。如果你谈论的是300次往返旅行，那就另当别论了，但是对于仅仅3次往返旅行，我认为这一定是一个过早优化的例子。

That said, the way I'd do this would probably be to executed the 3 stored procuedres using SQL:

也就是说，我这样做的方式可能是使用SQL执行3个存储的采购:

exec dbo.p_myproc_1 @param_1 = @in_param_1, @param_2 = @in_param_2
exec dbo.p_myproc_2
exec dbo.p_myproc_3

You can then iterate through the returned results sets as you would if you directly executed multiple rowsets.

然后，您可以像直接执行多个行集那样迭代返回的结果集。

#6

Build a temp-table? Insert all results into the temp table and then select * from @temp-table

建立一个临时表?将所有结果插入到临时表中，然后从@temp-table中选择*

as in,

如,

@temptable=....
select @temptable.field=mytable.field from mytable
select @temptable.field2=mytable2.field2 from mytable2

etc... Only one trip to the database, though I'm not sure it is actually more efficient.

等等……只去过一次数据库，不过我不确定它是否真的更有效率。

#1