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 issue concerns, feel free to mail me at neeraj_saluja@indiatimes.com.
------------------------------------------------------------------------------------------------------------------------------
Enjoy.... Enjoy as if there is no tomorrow.
Regards,
Neeraj Saluja
|
| Author: Sanjeev K Yadav 05 Aug 2004 | Member Level: Bronze Points : 0 |
It is a nice and brief artical on the subject.
|
| Author: Dhaval Mehta 26 Oct 2004 | Member Level: Bronze Points : 0 |
Hi ,
This is a very good artical. Will u please tell me how to measure performance improvement with Connection pooling
|
| Author: Neeraj Saluja 27 Feb 2007 | Member Level: Gold Points : 0 |
I have posted more details on Connection Pooling at the URL : http://www.codeproject.com/useritems/ADONET_ConnectionPooling.asp
|
| Author: Nagamohan kumar P 24 May 2008 | Member Level: Gold Points : 2 |
hi
This is Good Article Now i am having the same problem as my connection pool is reaching the maximum size . i need to keep running a job that fires for every 15 mins and in this i have many connections which open and close but even after closing the connection my connection pool is reaching the maximum size every often is there any other alternative
|
| Author: Arun Kumar 06 Jun 2008 | Member Level: Bronze Points : 2 |
onnection string plays a vital role in connection pooling. The handshake between ADO.NET and database server happens on the basis of this connection string only. Below is the table with important Connection pooling specific keywords of the connection strings with their description.
Name Default Description
Connection Lifetime 0 When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection Timeout 15 Maximum Time (in secs) to wait for a free connection from the pool
Enlist 'true' When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values are true, false, yes, and no. Set Enlist = "false" to ensure that connection is not context specific.
Max Pool Size 100 The maximum number of connections allowed in the pool.
Min Pool Size 0 The minimum number of connections allowed in the pool.
Pooling 'true' When true, the SQLConnection object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.
Incr Pool Size 5 Controls the number of connections that are established when all the connections are used.
Decr Pool Size 1 Controls the number of connections that are closed when an excessive amount of established connections are unused.
* Some table contents are extracted from Microsoft MSDN Library for reference.
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: daya 31 Mar 2009 | Member Level: Gold Points : 0 |
Good one.
|
| Author: Surya Kant 08 Apr 2009 | Member Level: Silver Points : 1 |
Connection pooling is means for pool of connection, by default server mantain the pool of connection, whenever user again opens the connection, connection is get from the pool of open connection, new connection is not created.
|
| Author: Gaurav Arora 09 Apr 2009 | Member Level: Diamond Points : 1 |
Hi,
This is a very good information,you have to post more articles like this.because almost everyone is facing this problem.
keep on posting.
Thanks & regards
Gaurav Arora
|
| Author: Deepika Haridas 11 Apr 2009 | Member Level: Diamond Points : 0 |
Hi,
Great Article..
Very informative..
Keep posting more...
|