If I have about 2000 record and I make a multiple insert. Which method has better performance than the other?
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.
I recently did a little tinkering with a very similar situation. I had an application that would insert anywhere from 1 to a couple thousand records into a MySQL database.
To demonstrate (to yourself), the connection pool behavior Magnus describes in his answer, you could whip up a quick application. With a button and on the click event, open (and then .close() ) a connection to your database, then click the button to "re-open" the connection a few times after that. (Make sure to give yourself some feedback so you know when (and if) .open() succeeded.) You'll notice the initial connection takes a few seconds, but subsequent attempts are very quick when the connection is grabbed from the pool.
Based on what my lead wanted, we ended up doing a 'bulk' insert by generating a big insert statement dynamically, for more of a "all or nothing" approach, rather than individual insert statements. At one point, I had two versions of our program using individual inserts, and also the bulk insert approach. For our situation, I didn't really see any noticable performance difference. (To be fair, there wasn't a ton of work being done either)
The default behavior of a connection is that it is added to a pool. So when Close() in called it is not really closed but just released to the pool and when Open() is called on a new connection, an existing open connection is picked from the pool.
If pooling is disabled having an open connection during the complete insert is preferred to closing it after each insert.
