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

Execute multiple SQL commands in one round trip

提问by tster

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".

现在我可以编写单个存储过程（“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。

采纳答案by Patrick Karcher

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.

您提到的单个多部分命令和存储过程选项是两个选项。您不能以它们在数据库上“并行化”的方式来执行它们。但是，这两个选项都会导致单次往返，所以你在那里很好。没有办法更有效地发送它们。在 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 canbe 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 extrainto 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.

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

回答by Darin Dimitrov

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());

    }
}

回答by Earlz

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

建立一个临时表？将所有结果插入到临时表中，然后select * from @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.

等等...只有一次访问数据库，虽然我不确定它实际上更有效。

回答by Remus Rusanu

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 doend 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 allusers, allteams and allpermissions) then you obviosuly have much bigger fish to fry before reducing the round-trips... reduce the resultsets first.

但是，如果您的查询确实是您发布的内容（即选择所有用户、所有团队和所有权限），那么在减少往返次数之前，您显然有更大的鱼要炒……首先减少结果集。

回答by Justin

Firstly, 3 round trips isn't really a big deal. If you were talking about 300round 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.

首先，3 次往返并不是什么大问题。如果您谈论的是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.

然后，您可以像直接执行多个行集一样遍历返回的结果集。

回答by Shimmy Weitzhandler

I this thislink 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.

考虑至少使用相同的连接打开；根据这里所说的，打开连接几乎是 Entity-Framework 中性能成本的最高领导者。