Performance Question

Obviously you do not want to have an open connection to a database
when your program is waiting for user input but if you are doing a
bunch of processing after a user request is it better to open and
close connections when you need access to the database or open once,
use multiple times and then close when you know you will not need
access again?

I guess what I am asking is, what is more important? Minimizing your
concurrent connections to a database or the performance hit of
repeated open and closing a connection.

I know there probably isn't one correct answer but what should I take
into consideration?

John.
The general rule of thumb here is "open late, close early".

Open your connection just before you need to use it, and close it as soon as possible. In my opinion, you would do this each time you need a connection. The reason for this is that by default, your connections are pooled. Normally, when you open a connection that uses an identical connection string as one that has already been opened and closed, you application will grab that connection again. Therefor, the connection does not need to be initialized and authenticated to SQL Server again.

Generally, I design my methods to perform as few database operations as possible with a goal of creating methods that are loosely coupled and highly cohesive. I create and open the connection within the method, use it, then close it. I have analyzed the connection performance with Performance Monitor and this seems to work well. Sometimes it is necessary to loop through some data, and process some database operation one record at a time. In this case I will usually open the connection just before the loop, and close it immediately after.

Ultimately, you may need to experiment a little for yourself. Every situation is different, and calls for different design considerations. You may be interested in having a look at theData Access Application Block from Microsoft for somebest practices.

Hope this is helpful.
|||I agree with NetProfit. I would like to add a couple of points to think on. Its not just for Db hits either, but you should also try to avoid chatty interface to the DB as well, to cut down on network roundtrips. I would also say that generally its better to get the whole result set back, close the connection ASAP and then work on the data. I've seen plenty of solutions that mis-use the datareader.|||Thanks. I had forgetten about connection pooling. I am looking forward to reading the Data Access Application Block article too.

John.