The process of establishing a database connection can be time
consuming depending upon network connectivity. Connection pooling is a viable
option if the network is an issue, and database server resources are
available.
There are
several steps involved with establishing a database connection. First, you will
establish a connection to the database server over the network. Next, the
connection string is parsed and the user authenticated. Finally, the connection
is established, and operations may be performed. Connection pooling allows an
application to maintain ownership of a database connection.
Connection
pooling maintains a group (or pool) of active database connections. When an
application tries to open a database connection, an open connection is retrieved
from the pool (if available). Closing a connection returns it to the pool for
other processes to utilise.
ADO.NET
pools connections with the same connection or configuration (connection
string). It can maintain more than one pool (actually, one for each
configuration). An interesting note: Connection pooling is utilised (by
default) unless otherwise specified. If you close and dispose of all
connections, then there will be no pool (since there are no available
connections).
While
leaving database connections continuously open can be troublesome, it can be
advantageous for applications that are in constant communication with a
database by negating the need to re-open connections. Some database
administrators may frown on the practice since multiple connections (not all of
which may be useful) to the database are open. Using connection pooling depends
upon available server resources and application requirements (i.e., does it
really need it).
Using
connection pooling
Connection pooling is enabled by default. You may override the default
behaviour with the pooling setting in the connection string. The following SQL
Server connection string does not utilise connection pooling:
Data Source=TestServer;Initial Catalog=Northwind;
User ID=Chester;Password=Tester;Pooling=False;
You can
use the same approach with other .NET Data Providers. You may enable it by
setting it to True (or eliminating the Pooling variable to use the default
behavior). In addition, the default size of the connection pool is 100, but you
may override this as well with connection string variables. You may use the
following variables to control the minimum and maximum size of the pool as well
as transaction support:
·
Max
Pool Size: The
maximum number of connections allowed in the pool. The default value is 100.
·
Min
Pool Size: The
minimum number of connections allowed in the pool. The default value is zero.
·
Enlist: Signals whether the pooler
automatically enlists the connection in the creation thread's current
transaction context. The default value is true.
The following
SQL Server connection string uses connection pooling with a minimum size of
five and a maximum size of 100:
Data Source=TestServer;Initial Catalog=Northwind;
User ID=Chester;Password=Tester;Max Pool Size=50;
Min Pool Size=5;Pooling=True;
You should
refer to the documentation if you're using a .NET Data Provider other than SQL
Server. Other data providers may have more pooling options. A good example is
the Oracle Data Provider, which offers two options -- Decr Pool Size and Incr
Pool Size -- for controlling how a connection pool may shrink or grow.
The
Microsoft documentation states that the connection pooler satisfies requests
for connections by reallocating connections as they are released back into the
pool. If the maximum pool size has been reached and no usable connection is
available, the request is queued. The pooler then tries to reclaim any
connections until the time-out is reached (the default is 15 seconds). If the
pooler cannot satisfy the request before the connection times out, an exception
is thrown.
Pooling
advice
You should be judicious in your use of connection pooling. Here are a few tips
when using it:
·
Only
open connections when needed. That is, timing is everything, so open a
connection just before you need it and not any sooner. Also, close that
connection as soon as you are finished with itâ€"don't wait for the
garbage collector to do it.
·
Close
user-defined transactions before closing related connections.
·
To
maintain the connection pool, you should keep at least one connection open.
Therefore, do not close all your connections in the pool. If server resources
become a problem, you may close all connections, and the pool will be recreated
with the next request.
·
Do
not use connection pooling if integrated security is utilised. This results in
a unique connection string per user, so each user has a connection pool that is
not available to other users. The end result is poor performance, so pooling
should be avoided in this scenario.
ADO.NET
2.0
Two new connection pooling-related methods have been introduced with ADO.NET
2.0. They are ClearAllPools and ClearPool. ClearAllPools clears the connection
pools for a given provider, and ClearPool clears the connection pool that is
associated with a specific connection. If there are connections in use at the
time of the call, they are marked appropriately. When they are closed, they are
discarded instead of being returned to the pool.
Another
application option
It may seem odd that a column focusing on cleaning up database resources is
followed by another one outlining connection pooling. The key is to know what
is available and to use it appropriately depending on application requirements.
If an application is in constant communication with a database, then connection
pooling may be optimal since the need to open/establish connections is negated,
thus performance improves. On the other hand, an application that runs nightly
does not need to maintain a pool since it does nothing with the database the
rest of the day. Use your best judgment when deciding whether to use connection
pooling.
PLEASE REFER: http://www.builderau.com.au/program/dotnet/soa/Take-advantage-of-Net-connection-pooling/0,339028399,339269541,00.htm