每一个nonquery之前打开的连接或整个一个连接？ [英] open connection before every single nonquery or one connection for the whole?

问题描述

如果我有关于 2000记录和我做一个多重插入。哪种方法比其他更好的性能？

• 每个单个插入插入后。而密切的联系。


• 对于整个​​大宗一个连接并关闭在连接
  结束。约在这种情况下，连接超时什么。

备注

• 该数据库是Informix的数据库。




• 大约需要3.5到4分钟插入约6000纪录。（用
  第一种方法）

解决方案

应用程序连接池将在很大程度上使这个问题不相干为C＃应用程序池进行多次调用同一个数据库进行了优化。

这是说，我所做的事情符合以下规则：

如果你可以打开一个连接，然后通过使用使用的语法做多的操作：

 使用（SqlConnection的连接=新的SqlConnection（的connectionString））
{
    connection.Open（）;
    //在这里做的工作;连不上上以下行。
}
 

然后保持连接打开，做所有的操作。

但是，如果每个记录操作，然后保存在你打开每一点新的连接这样的方式，也不必担心过多。你还是会用连接池进行很大的。

从MSDN：

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

部署高性能的应用程序，您必须使用连接池。当您使用SQL Server的.NET Framework数据提供，你不必启用连接池，因为提供程序自动管理这一点，虽然你可以修改一些设置。有关详细信息，请参阅SQL Server连接池（ADO.NET）。

即使你的连接不上的SQL之一，建在连接提供商为其它来源的行为方式相似。

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

• connection with each single insert .and close after the insertion .
• one connection for the whole bulk and close the connection at the end. and what about the connection timeout in this case.

Notes :

• The database is informix db.

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

解决方案

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.

From MSDN:

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

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

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

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