keeping one connection to DB or opening closing per need

Question

I looking for a best practice in following case, with reasons why each way is better.

I have one DB with about 10~20 client applications that connecting to one main DB server.

There can be about 200 calls from one client to the DB per minute, in really rare cases.

The application are multithreaded, about 20 threads per application.

What will be best practice here to keep only one connection to the DB per application and reuse it per application . OR opening new connections per needed call and close them fast.

We are working with oracle and sql-server.

The DBA at my work told me that it can take a while to open connections, so it is best to reuse connections when possible. Might be old info, though - not sure :) There is a middle-of-the-road option you didn't mention, to create connection pool. That way you can have multiple connections, yet leave connections open for performance. — Merlyn Morgan-Graham, Jun 01 '11 at 06:20

score 3 · Accepted Answer · answered Jun 01 '11 at 06:19

The .NET oracle provider has built-in connection-pooling capabilities. Whenever you need a DB connection, create a new one do the work and release it immediately. The connection pooling will take care of reusing connections efficiently.

The best way to release the connection is through the using construct which will ensure that the connection is disposed, even if exceptions occur.

using(OracleConnection connection = ConnectionFactory.Create())
{
    connection.DoStuff();

} //connection.Dispose() called here.

On top, 200 is not a lot per minute. I normally put in between. Like pges open a connection ONCE, then close it when finished. But i dont keep spare connections around normally. — TomTom, Jun 01 '11 at 10:59

score 1 · Answer 2 · answered Jun 01 '11 at 06:19

The best practice is to dispose connection as soon as possible. Technically connection will not be closed, just returned to the pool and will be reused by other threads.

SQL Server Connection Pooling (ADO.NET)

Quote from MSDN:

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement.

score 0 · Answer 3 · answered Jun 01 '11 at 10:32

It is ultimately about where (if anywhere) you need to throttle your connections. In a pure web app, you can't take the risk of several thousand people hitting POST at the same time. On the other hand, an internal business app may only have half a dozen users.

Can your database cope with 20 clients all making 200 calls per minute ? Will the separate threads make parallel calls to the database ?

I'd tend towards each client having its own connection pool.

Are there some clients which are more important than others ? If so, then you might want one client with 20 connections and the others with 10.

Any decent DB setup should be able to cope with a couple of hundred connections and not fall over if they all try to ping at once.

keeping one connection to DB or opening closing per need

3 Answers3

Linked