To effectively use connection pooling, you need to understand the concepts underlying connection pooling, how connection pooling is implemented by the major .NET data providers, and how to ensure that connection pooling is used by an application.
Connection pooling allows an application to reuse connections from a pool instead of repeatedly creating and destroying new connections. Connection pooling can significantly improve the performance and scalability of applications by allowing a smaller number of connections to service the connection requirements of an application and because the overhead of establishing a new connection is eliminated.
A connection pool is created for each unique connection string. An algorithm associates items in the pool based on an exact match with the connection string; this includes capitalization, order of name value pairs, and even spaces between name/value pairs. Dynamically generated connection strings must be identical so that connection pooling is used. If delegation is used, there will be one pool per delegate user. When transactions are used, one pool is created per transaction context. (For more information, see Recipe 1.17.) When the connection pool is created, connection objects are created and added to the pool to satisfy the minimum pool size specified.
When a connection is requested by an application and the maximum pool
size has been reached, the request is queued. The request is
satisfied by reallocating a connection that is released back to the
pool when the
Connection is closed or disposed.
The connection pool manager removes expired connections and
connections that have had their connection with the server severed
from the pool.
Connection object should be closed as soon as
it is no longer needed so that it is added to or returned to the
connection pool. This is done by calling either the
Close( ) or
Dispose( ) method of the
Connection. Connections that are not explicitly
closed might not be added to or returned to the connection pool.
DataAdapter automatically opens and closes a
Connection as required if it is not already open
when a method such as
FillSchema( ), or
Update( ) is
Connection must be explicitly closed
if it is already open prior to the
The following subsections detail connection pooling for specific .NET Framework data providers.
The .NET data providers for SQL Server and Oracle provide efficient, transaction-aware support for connection pooling. Pools are created for each process and not destroyed until the process ends. Connection pooling is enabled by default.
Controlling SQL Server and Oracle .NET data provider connection pooling with connection string attribute/value pairs is discussed in Recipe 1.16.
The default OLE DB services that are enabled for a provider are
specified by the value for the registry
CLSID>\OLE_DBSERVICES DWORD value. Table 1-4 describes the alternatives.
Table 1-4. OLE DB services enabled values
All services (default).
All services except Pooling and AutoEnlistment.
All services except Client Cursor.
All services except Pooling, AutoEnlistment, and Client Cursor.
No aggregation. All services are disabled.
You can override the default OLE DB provider services by specifying a
value for the
Services attribute in the connection string. Table 1-5 describes possible values.
Table 1-5. OLE DB services connection string values
OLE DB Services attribute value
Default services enabled
All services (default)
All services except Pooling and AutoEnlistment
All services except Client Cursor
All services except Pooling, AutoEnlistment, and Client Cursor
The following three configurable settings control OLE DB connection pooling:
The length of time in seconds that an unused connection remains in the pool before it is released. This can be configured for each provider and defaults to 60 seconds.
The length of time in seconds before an attempt to acquire a connection is reattempted when the server is not responding. This is global to all providers and defaults to 64 seconds.
The factor by which the retry wait time is increased when a connection attempt fails before reattempting the connection. This is global to all providers and defaults to a factor of 2.
OLE DB connection pooling is enabled by default; you can control it in three different ways:
Specify a value for the
OLE DB Services attribute
in the connection string.
Edit the registry to enable or disable pooling for an individual provider or globally by changing registry values. For more information, see Recipe 1.16.
Use the OLE DB API (Application Programming Interface) from an
application to enable or disable connection pooling. The
Wait can be configured programmatically only by
manipulating the registry entries. For more information about the OLE
DB API, see the OLE DB Programmer’s
Reference in MSDN Library.
The ODBC .NET data provider pools connections by using the connection pooling provided by the ODBC Driver Manager (DM). Pooling parameters for an ODBC driver affect all applications that use that driver, unless changed from within a native ODBC application.
The following two configurable settings control ODBC connection pooling:
The length of time in seconds that an unused connection remains in the pool before it is released.
The length of time before an attempt to acquire a connection is reattempted when the server is not responding.
Connection pooling is enabled by default. You can enable, disable, and configure it in three ways:
introduced with ODBC 3.5 (MDAC 1.5), to enable or disable pooling for
the entire driver and to control the
Edit the registry. For more information, see Recipe 1.16.
Use the ODBC API from an ODBC application to limit the scope of pooling to the environment handler or to the driver, and to configure other pooling options. For more information about the ODBC API, see the ODBC Programmer’s Reference in the MSDN Library.