Geek Answers Handbook

An open connection before every single request or one connection for everything?

If I have about 2000 record and I do multiple insertion. Which method is better than the other?

  • connection with each separate insert. and close after insertion.
  • one connection for the entire batch and closing 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-4 minutes to insert about 6000 records (with the first method)

+1
source share
3 answers

The application connection pool will largely make this issue inappropriate, since C # application pools are optimized for multiple calls to the same database.

However, what I am doing is following the following rule:

If you can open the connection, then perform several operations using the syntax "using":

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

Then open the connection and complete all the operations.

If, however, each entry is managed and then saved in such a way that you need to open a new connection at each point, do not worry too much. You still do a great job with the connection pool.

From MSDN:

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

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

Even if your connection is not SQL, the built-in connection providers for other sources behave similarly.

+1
source

Recently, I worked a bit with a very similar situation. I had an application that would insert from 1 to several thousand records into a MySQL database.

To demonstrate (for yourself) the behavior of Magnus's connection pool in his answer, you can hack a quick application. Use the button and the click event to open (and then .close ()) the connection to your database, and then click the button to “re-open” the connection several times after that. (Make sure you get some feedback so you know when (and if) .open () succeeded.) You will notice that the initial connection takes a few seconds, but subsequent attempts are very quick when the connection is taken from the pool.

Depending on what I needed to do, we ended up working with the "big" insert, creating a dynamic big insert statement for more "all or nothing", rather than individual insert statements. At some point, I had two versions of our program, which used separate inserts, as well as the volume insert method. In our situation, I did not see a noticeable difference in performance. (In fairness, there wasn’t even a ton of work)

+2
source

The default behavior of a connection is that it is added to the pool. Therefore, when Close() in a call it is not closed, but only released into the pool and when Open() is called in a new connection, an existing open connection is selected from the pool.
If the pool is disconnected with an open connection during a full insert, it is preferable to close after each insert.

+1
source

More articles:


All Articles