在每个非查询或整个连接之前打开连接? [英] open connection before every single nonquery or one connection for the whole?

问题描述

如果我有大约 2000 条记录 并且我进行了多次插入.哪种方法的性能比另一种更好?

If I have about 2000 record and I make a multiple insert. Which method has better performance than the other?

• 与每个插入连接.插入后关闭.
• 一个连接用于整个批量，并在结尾.以及这种情况下的连接超时情况.

注意事项:

• 数据库是informix db.

• The database is informix db.

插入大约 6000 条记录大约需要 3.5 到 4 分钟.(使用第一种方法)

It takes about 3.5 to 4 minutes to insert about 6000 record.(with the first method)

推荐答案

应用程序连接池将在很大程度上使这个问题变得无关紧要，因为 c# 应用程序池针对对同一数据库的多次调用进行了优化.

Application connection pooling will largely make this question irrelevant as c# application pools are optimized for multiple calls to the same database.

也就是说，我所做的符合以下规则:

That said, what I do conforms to the following rule:

如果您可以打开一个连接，然后使用使用"语法进行多项操作:

If you can open a connection and then do multiple operations by use of the "using" syntax:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here; connection closed on following line.
}

然后保持连接打开并执行所有操作.

Then keep the connection open and do all your operations.

但是，如果每条记录都被操作然后保存，以至于您必须在每个点打开一个新连接，请不要担心太多.您仍然会在连接池方面表现出色.

If however, each record is manipulated and then saved in such a fashion that you have to open a new connection at each point, don't fret overmuch. You'll still perform great with connection pooling.

来自 MSDN:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

要部署高性能应用程序，您必须使用连接池.当您使用 SQL Server 的 .NET Framework 数据提供程序时，您不必启用连接池，因为提供程序会自动管理它，尽管您可以修改一些设置.有关详细信息，请参阅 SQL Server 连接池 (ADO.NET).

To deploy high-performance applications, you must use connection pooling. When you use the .NET Framework Data Provider for SQL Server, you do not have to enable connection pooling because the provider manages this automatically, although you can modify some settings. For more information, see SQL Server Connection Pooling (ADO.NET).

即使您的连接不是 SQL 连接，其他来源的内置连接提供程序也会以类似的方式运行.

Even if your connection isn't the SQL one, the built in connection providers for the other sources behave in similar manner.

这篇关于在每个非查询或整个连接之前打开连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！