ODBC Connection Pooling

The operation of establishing a connection to a database can be expensive. In applications that repeatedly open connections with similar parameters, this cost can affect overall application performance. To address this problem, ODBC provides what is known as “Connection Pooling”. In connection pooling, connections that are closed by the application are not actually closed, but instead are reset (clearing all the server-side information stored for that connection), and then kept in a “pool”. When a request comes from the application to create a new connection, ODBC will check to see if a connection already exists in the pool whose parameters match those of the incoming connection request. If that is the case, then instead of opening a new connection the existing “pooled” connection is used.

How Connection Pooling works

When a request comes in, pooling logic:

- Looks for the first unlocked pool and locks the pool

- Does a “byte by byte” comparision of the entire connection string + SID through existing connections to determine whether to make a new connection or to reuse an existing one

- If a match is found, pooled connection is given, or else a new one is created and pool is unlocked.

Connection Pooling is stateless

Avoid operations that change connection state, such as:

- Changing databases
USE Pubs

- Changing settings
SET NOCOUNT ON

- Creating temporary objects

Such objects will linger within the life of the connection in the pool

Useful Connection Pooling settings

- CPTimeout value

Time the connection remains “unused” in the pool in seconds

This setting is driver-specific

- Retry Wait

Time the connection pool will be blocked when it is determined that the server is not responding.

Configuring Connection Pooling

- Data Sources (ODBC) administrator (ODBCAD32), pooling can be enabled or disabled for specific driver.

- Registry, Settings can be modified via registry.

- Code, Calling application can change pooling options.

Switch to the Connection Pooling tab

Double clicking on a driver displays details

Modifying settings via Windows Registry

- CPTimeout can be changed at driver level

HKLM\Software\Odbc\Odbcinst.ini\<drivername>

Value set in seconds

Default value is 60 seconds

- Modifying settings via registry may be a support issue as the same code may behave differently in two different boxes.

Configuring Pooling using ODBC API

- SQL_CP_ONE_PER_HENV

Single pool for each hEnv (Environment handle)

- SQL_CP_ONE_PER_DRIVER

Separate connection pool is supported for each driver

- If using SQLDriverConnect(), need to use SQL_DRIVER_NOPROMPT otherwise connection will not be pooled.

C++ Sample

SQLSetEnvAttr(NULL,SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER) SQL_CP_ONE_PER_DRIVER,SQL_IS_INTEGER);

rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);

// Set the ODBC behavior version.
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC2, SQL_IS_INTEGER);

// Set the matching condition for using an existing
// connection in the pool
SQLSetEnvAttr(henv,SQL_ATTR_CP_MATCH,(SQLPOINTER)
SQL_CP_RELAXED_MATCH, SQL_IS_INTEGER);

VB Sample

Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal henv As Long, ByVal Attribute As Long, ByVal Value As Long, ByVal StringLength As Long) As Integer

Public Const SQL_ATTR_CONNECTION_POOLING = 201
Public Const SQL_ATTR_CP_MATCH = 202
Public Const SQL_CP_ONE_PER_DRIVER = 1
Public Const SQL_IS_UINTEGER = -5

ret = SQLSetEnvAttr(0, SQL_ATTR_CONNECTION_POOLING, _
SQL_CP_ONE_PER_DRIVER, SQL_IS_UINTEGER)
' Follow by SQLAllocEnv, SQLAllocConnect calls