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
- 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
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
Single pool for each hEnv (Environment handle)
Separate connection pool is supported for each driver
- If using SQLDriverConnect(), need to use SQL_DRIVER_NOPROMPT otherwise connection will not be pooled.
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
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, _
' Follow by SQLAllocEnv, SQLAllocConnect calls