An Intro to Connection Pooling

Related Articles

Abstract :

     Pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a pool, an application can reuse that connection without performing the complete connection process.

     When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released.

How to achieve connection pooling:

     Be sure than your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened, and connection pooling won't be used.

Pros :

     The main benefit of pooling is performance. Making a connection to a database can be very time-consuming, depending on the speed of the network as well as on the proximity of the database server. When pooling is enabled, the request for a database connection can be satisfied from the pool rather than by (re)connecting to the server, (re)authenticating the connection information, and returning (again) a validated connection to the application.

Cons :

               Multiple connections, all of which may not be of use, to the database are open.

Tips and Tricks for better use of connection pooling :      

*      Open a connection only when you need it, not before. *      Close your connection as soon as you are done using it. Don't wait for garbage collector to do it.
*      Be sure to close any user-defined transactions before closing a connection.
*      Do not close all your connections in the pool, keep at least one connection alive. Well, if memory and other resources is the primary concern then need not do this, the pool will be recreated with the next request.

Connection Pooling FAQs :

1. When is the connection pool created :

     When a connection is opened for the first time a connection pool is created and the pool is determined by the exact match of the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is requested, if the connection string is not an exact match to an existing pool, a new pool is created.      

2. When is the connection pool destroyed :
      When last connection in the pool is closed the pool is destroyed.

3. What happens when all the connections in the connection pool are consumed and a new connection request comes :
     If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when you call Close or Dispose on the Connection.

4. How can I enable connection pooling ?
     For .Net applications it is enabled by default. Well, to make sure the same we can use the Pooling=true; in the connection string for the SQLConnection Object.

5. How can I disable connection pooling?

     ADO.NET Data Providers automatically use connection pooling turned on. If you want to turn this functionality off:

     In an SQLConnection object, Add this to the connection string:

     Pooling=False;      

     In An OLEDBConnection object, add this:

     OLE DB Services=-4;

     This way, the OLE DB data provider will mark your connection so that it does not participate in connection pooling.  

 

------------------------------------------------------------------------------------------------------------

In case of any concerns, feel free to mail me at neeraj_saluja@indiatimes.com

------------------------------------------------------------------------------------------------------------

 

Enjoy?Enjoy as if there is no tomorrow.

 

 

Regards,

Neeraj Saluja

 


Publication Date: Tuesday 11th May, 2004
Author: Neeraj Saluja View profile

Related Articles