CODEWITHSUNDEEP
.netsql-serverdatabaseoracle-databasesql-server-2005
Night Walker
Night Walker

Reputation: 21260

keeping one connection to DB or opening closing per need

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.

Upvotes: 3

Views: 587

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 0

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 3

Alex Aza
Alex Aza

Reputation: 78457

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.

Upvotes: 1

Related Questions