What really is connection pooling?

Question

I have heard the term connection pooling and looked for some references by googling it... But can't get the idea when to use it....

• When should i consider using connection pooling?

• What are the advantages and disadvantagesof connection pooling?

Any suggestion....

Answer 1

The idea is that you do not open and close a single connection to your database, instead you create a "pool" of open connections and then reuse them. Once a single thread or procedure is done, it puts the connection back into the pool and, so that it is available to other threads. The idea behind it is that typically you don't have more than some 50 parallel connections and that opening a connection is time- and resource- consuming.

Answer 2

connection pooling enables re-use of an existing, but not used database connection. by using it you eliminate the overhead of the connection/disconnection to the database server. it provides a significant performance boost in most cases. the only reason i can think of not to use it is if your software won't be connecting frequently enough to keep the connections alive or if there's some bug in the pooling layer.

-don

Answer 3

If you mean .NET, see:

• Connection Pooling (ADO.NET)
• SQL Server Connection Pooling (ADO.NET)
• OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET)
• Conneciton Pooling Search

Answer 4

You should use connection pooling whenever the time to establish a connection is greater than zero (pretty much always) and when there is a sufficient average usage such that the connection is likely to be used again before it times out.

Advantages are it's much faster to open/close new connections as they're not really opened and closed, they're just checked out/in to a pool.

Disadvantage would be in some connection pools you'll get an error if all pooled connections are in use. This usually is a good thing as it indicates a problem with the calling code not closing connections, but if you legitimately need more connections than are in the pool and haven't configured it properly, you could get errors where you wouldn't otherwise.

And of course there will be other pros and cons depending on the specific environment you're working in and database.

Answer 5

In .NET, if you are using the same connection string for data access then you already have connection pooling. The concept is to reuse an idle connection without having to tear it down & recreate it, thereby saving server resources. This is of-course taking into consideration that you are closing open connections upon completion of your work.

Answer 6

When should i consider using connection pooling?

• Always for production system.

What are the advantages and disadvantages of connection pooling?

Advantages:

• Performance. Use a fixed pool of connection and avoid the costly creation and release of connections.
• Shared infrastructure. If your database is shared between several apps, you don't want one app to exhaust all connections. Pooling help to limit the number of connection per app.
• Licensing. Depending on your database license, the number of concurrent client is limited. You can set a pool with the number of authorized connections. If no connection is available, client waits until one is available, or times out.
• Connectivity issue. The connection pool that is between the client and the database, can provide handy features such as "ping" test, connection retry, etc. transparently for the client. In worse case, there is a time-out.
• Monitoring. You can monitor the pool, see the number of active connections, etc.

Disadvantage:

• You need to set it up and configure it, which is really peanuts usually.