Taking Advantage of Connection Pooling

Problem

You need to understand connecting pooling and make sure that your applications use it.

Solution

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.

Discussion

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.

The 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.

The DataAdapter automatically opens and closes a Connection as required if it is not already open when a method such as Fill( ) , FillSchema( ) , or Update( ) is called. The Connection must be explicitly closed if it is already open prior to the DataAdapter operation.

The following subsections detail connection pooling for specific .NET Framework data providers.

SQL Server and Oracle

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.

OLE DB

The OLE DB .NET data provider pools connections by using resource pooling provided by the OLE DB core components .

The default OLE DB services that are enabled for a provider are specified by the value for the registry HKEY_CLASSES_ROOTCLSID 's CLSID>OLE_DBSERVICES DWORD value. Table 1-4 describes the alternatives.

Table 1-4. OLE DB services enabled values

OLE_DBSERVICES value	Description
0xffffffff	All services (default).
0xfffffffe	All services except Pooling and AutoEnlistment.
0xfffffffb	All services except Client Cursor.
0xfffffff0	All services except Pooling, AutoEnlistment, and Client Cursor.
0x00000000	No services.
missing value	No aggregation. All services are disabled.

You can override the default OLE DB provider services by specifying a value for the OLE DB 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
-1	All services (default)
-2	All services except Pooling and AutoEnlistment
-5	All services except Client Cursor
-7	All services except Pooling, AutoEnlistment, and Client Cursor
	No services

The following three configurable settings control OLE DB connection pooling:

SPTimeout

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.

Retry Wait

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.

ExpBackOff

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 SPTimeout and Retry 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.

ODBC

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:

CPTimeout

The length of time in seconds that an unused connection remains in the pool before it is released.

Wait Retry

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:

• Use the ODBC Data Source Administrator , introduced with ODBC 3.5 (MDAC 1.5), to enable or disable pooling for the entire driver and to control the CPTimeout and Wait Retry settings.
• 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.