Pooling

INTRODUCTION

Terms

• MDAC: An architecture that provides a universal framework for exposing both traditional SQL-based databases and non-SQL data sources (such as email and documents) as long as data can be exposed in tabular form
• Pooling: Enabling an application to use a data source connection from a pool of connections that do not need to be re-established for each connection. Once a connection to a data source has been made and placed in the pool, an application can reuse without having to perform the complete connection process.

Overall Mechanism

The overall process of pooling works as follows: As applications open connections to a data source and subsequently release them, a pool of open connections with complete authentication and connection properties is built. Then if an application requests the same data store with the same authentication and connection properties, the connection will be satisfied from the pool, if available, rather than creating the connection again. Connections are held in the pool for a specific time after the application releases them. They are released by the pooling mechanism when they time out.

Benefits

Main benefit to connection pooling is performance - Establishing a connection to a database can be very expensive depending on:

• The proximity of the database server.
• The speed of the network.

Pool Contents

In general, all data source connections that share the same exact properties (i.e., connection string in ADO, ODBC properties, and OLE DB properties) are placed in the same pool. If a connection had a different property or even an extra property, it is placed in a different pool.

closing Recordset & Connection objectS

The close method on a Recordset or Connection object releases resource associated with that object from memory. The object is still valid (i.e., still in memory). Setting the object to Nothing destroys the object and removes it from memory

POOLING BENEFITS

Overview

Any application that makes frequent calls to a data source for connections can benefit from pooling

Details

Note the following:

• Web application and n-tier applications benefit mostly from pooling.
• A given connection either resides in an OLE DB provider pool or in an ODBC provider pool, but not both.
• When a user releases a connection, it is returned to the pool rather than released

ODBC POOLING

Overview

When using ODBC drivers, pooling proceeds transparent to the application as follows: 

• When the first request for a connection is made, the connection passes through the pooling components and all connection properties are cached.
• The connection is then passed to the back-end server and a live connection is then given back to the user. The application now proceeds as usual.
• When the application releases the connection, the pool returns S_OK to the user to simulate a successful disconnection from the database. The actual connection is not released but is now waiting in the pool.
• When the next request for a connection to the database comes in, it will be satisfied from the pool, if that connection request has the same exact attributes as that in the pool.
• If the connection in the pool has timed out or if this incoming connection request does not match a connection (property-wise) in the pool, a new connection will be opened.

Configuring ODBC pooling

ODBC pooling can be configured in three different ways:

• ODBC Data Source Administrator (preferred method): Always use this option unless writing a native ODBC application
• Pooling can be enabled/disabled for the entire driver.
• Timeout value (amount of time that a connection remains unused in the pool), and Wait Retry value (amount of time that the connection pool is blocked when the server is not responding) can be specified.
• Timeout value is specified to avoid connection creep which causes the database server to refuse new connection requests when it hits its limit on available connection.
• Wait Retry applies to all drivers in the system. This value is used in multithreaded environments to avoid blocking situations: Assume a connection comes in, gets pooled, but the server is not responding. Assume further that 10 more connections come in that also get pooled, but with the server not responding, they create a race condition where none of the connection requests can succeed. With the Wait Retry value, when the first connection request comes in and the server is not responding, all other requests will fail with E_FAIL, but the first request will be blocked and retried every n seconds as specified in the Wait Retry value.

Registry

This is only recommended for pre MDAC 1.5. Use the following registry key to set the request values:

 HKEY_LOCAL_MACHINE/SOFTWARE.ODBC/ODBCINST.INI/

Native ODBC APPLICATION (ODBC API)

All actions performed with the ODBC administrator can be accomplished with an application using ODBC API. Further, pooling can be fine grained from being applicable to a specific driver or to being applicable to a specific environment handle.

Number of pools

With ODBC pooling, 

number of available pools = number of available processors

Irrespective of the number of pools, a single pool can contain pooled connections to different drivers. And for specific driver, the pool can contains pooled connection to different data sources.

Monitoring pooling

Use PerfMon system application to monitor pools and their contents

Database Context

Always maintain the original database context before disconnection: Assume your connection points to the pubs database. In your application you then decide to access the Master database to get some metadata for example, and then disconnect. The database context has changed from pubs to Master and the connection with this context change is returned to the pool. This may cause problems in the pool. See MSDN article Q197459, "PRB: ADO 2.0 Returns Connection Messages to Browser."

OLE DB POOLING

Overview

When using OLE DB providers, pooling proceeds transparent to the application as follows:

• An application creates an OLE DB Data Source object (using ADO or OLE DB API)
• OLE DB Core Services receive this request and provide a proxy data source object to the application. The application deals with this proxy object as if it was the actual data source object, but setting properties on this proxy, merely caches them in the proxy object.
• When the application opens a connection in ADO (or calls IDBInitialize::Initialize in OLE DB), the proxy data source object checks if any connections that match the connection information already exist and are not in use.
• If so, the proxy data source object uses the existing initialized data source object. If not, the application creates a new object, sets its properties, and connects to the database.
• When the application releases the data source object it is returned to the pool.
• Any data source object released by the application and not reused after the default 60 seconds is automatically released from the pool.

Configuring OLE DB pooling

ODBC pooling can be controlled in different ways:

• Pooling can be disabled for a specified provider through the registry -
  • The capabilities of each OLE DB provider are listed under HKEY_CLASSES_ROOT/CLSID/clsid where clsid is that of the OLE DB provider. The named value OLEDB_SERVICES is bit-mapped DWORD specifying the capabilities of the provider. See MSDN article Q245267, 'DOC: "OLEDB_SERVICES = -2" does Not Disable Autoenlistment' for a list of possible values.
  • The timeout should be entered as SPTimeout (DWORD, and in seconds) under HKEY_CLASSES_ROOT/CLSID/clsid where clsid is that of the OLE DB provider.
  • The retry wait should be entered as Retry Wait ((DWORD, and in seconds) under HKEY_LOCAL_MACHINE/SOFTWARE/Micorsoft/DataAccess/Session Pooling.
• OLE DB API directly through the properties you set when connecting to a database.
  • Create the data source object using IDataInitialize or IDBPromptInitialize.
  • With OLE DB, use the DBPROP_INIT_OLEDBSERVICES property to enable/disable various core components including pooling, transaction auto-enlistment, etc.
  • SPTimeout and Retry Wait cannot be programmatically configured using OLE DB. Manipulate the registry manually or with Win32 SDK.
  • Note: Using IDBInitialize::Uninitialize turns off pooling. Using IDBInitialize::Release releases the connection.
• From the connection string.
  • Using ADO, use the 'OLE DB Services' attribute in the connection string –

    "Courier New">oRS.Open "DATA SOURCE=sql1;DATABASE=pubs; OLE DB SERVICES =-1;"

  • With ADO we must keep at least one connection open for each unique user.
  • Automatically when a consumer accesses by a provider through MTS/COM+/IIS. 

Note on Retry Wait value

This value works differently than in ODBC - If the server is unavailable, the pool is blocked by OLE DB. The next retry for a valid connection occurs after one minute. If this attempt fails, the The next retry occurs after two minutes, and again after five minutes. Thereafter, every five minutes until a valid connection is returned.

Note

Pooling will not be enabled if CoCreateInstance is called on the CLSID of the provider

CoCreateInstance(CLSID_MSDASQL, ...);

Number of Pools

In OLE DB a given pool contains connection for a single provider (say OLE DB For SQL Server) with a specific set of connection attributes. That is, each pool can only contains connections to one set of attributes and one data source.

No Of Pools = (No of Processors + 1) x No of distinct sets of connection attributes.

OLE DB Tips

ADO

• ADO Connection object implicitly uses IDataInitialize. This means keep at least one instance of the Connection object instantiated for each unique user at all times. Otherwise, the pool is destroyed when the last Connection object is closed.
• MTS/COM+ are exceptions: Pool is destroyed only if all the connections in the pool have been closed by clients and are allowed to time out.
• ADO Connection objects are placed in the pool at the time the connection object is closed with close.
• When opening a Connection object, use close to close it at the end of the application lifetime. 

OLE DB

• Create the data source object using IDataInitialize or IDBPromptInitialize. As long as one instance of either interfaces exist, a pool is available to the application.
• Do not request prompting
• Use IDBInitialize::Uninitialize for flushing the connection from the pool. Use IDBInitialize::Release to release the connection.

TroubleShooting

Connection Creep

An increase in the number of open connections on a database server, i.e., SQL Server. Common causes

• Connection and/or Recordset objects are not closed using close method. Using close will cause the connection to be placed back in the pool.
• A disconnected recordset (its ActiveConnection = Nothing) was used with CursorLocation = adUserServer.
• A record set is not disconnected causing it to maintain a connection to the database server. The connection cannot be placed in the pool.
• An ADO object (especially the Connection object) was not closed properly (always use close to close objects after being done with them.)

Closing Connections

• ADO Connection objects are placed in the pool at the time the connection object is closed with close.
• Always explicitly close any object you open.

Disconnecting record set objects

• For a disconnected recordset, in order to release the connection to the pool:
  • cursor type must be adUseClient.
  • locking must be BatchOptimistic
  • .ActiveConnection set to Nothing.

Set oRS.ActiveConnection = Nothing    'Disconnect recordset
oCN.close                             'Return connection to pool
set oCN = Nothing                     'Release connection from pool

• A recordset with a server-cursor cannot be disconnected.

Temporary Tables

If the connection is used to create some temporary tables (#TableName) for server-side processing, they will not be destroyed until the connection is released from the pool – not just returned to the pool.

Connection Status

The status of any connection can be retrieved with DBPROP_CONNECTIONSTATUS property.

Persistent Connection for Resource Pooling

• Keep at least one connection open for each set of connection attributes.
• Do not open multiple unnecessary connection objects or multiple recordset object.
• Do not use one connection object for several hundred ASP pages. o 
• Do not share the Connection object across all threads. Use one thread, one Connection object and one persistent connection in the application to keep the pool alive.
• These issues do not apply to MTS/COM+ since MTS/COM+ enable the pool irrespective of having one or many connection objects.