OLE DB Resource Pooling

OLE DB resource pooling, also known as OLE DB session pooling, is handled by the OLE DB core components. To take advantage of resource pooling, an OLE DB consumer must invoke the data provider by using either the IDataInitialize or the IDBPromptInitialize interface. (ADO will attempt to do this automatically.) OLE DB resource pooling can be turned on for one provider and off for another.

Note   Performing CoCreateInstance on IDBInitialize is traditionally used in OLE DB to open a data source object. To use resource pooling, you perform CoCreateInstance on either IDataInitialize or IDBPromptInitialize. Both interfaces are part of the OLE DB service components and not your OLE DB data provider. You can use IDataInitialize and IDBPromptInitialize to retrieve an instance of IDBInitialize from your data provider so that the service components can be used. The two interfaces make it possible to use service component features such as pooling, transaction enlistment, and the Client Cursor Engine.

When the application creates an OLE DB data source object (via ADO or an OLE DB consumer), OLE DB services query the data provider for supported information and provide a proxy data source object to the application. To the consuming application, this proxy data source object looks like any other data source object, but in this case setting properties merely caches the information in the local proxy. When the application calls IDBInitialize::Initialize in OLE DB or opens a connection in ADO, the proxy data source object checks whether any connections already exist that match the specified connection information and are not in use. If so, rather than creating a new object, setting properties, and establishing a new connection to the database, the proxy data source object uses the existing initialized data source object. When the application releases the data source object, it is returned to the pool. Any data source object that is released by the application and not reused after 60 seconds is automatically released from the pool.

Figure 4 shows how OLE DB resource pooling works.

Figure 4. The Benefits of OLE DB Resource Pooling

Note   The term resource pooling is actually something of a misnomer. It implies that any kind of resource can be pooled, when in fact OLE DB resource pooling is just for the pooling of an OLE DB data source proxy object (DPO). Resource pooling does use the resource dispenser found with Microsoft Transaction Server, which is a more generic form of pool manager. More than a few OLE DB users have been confused by the term "resource pooling" into thinking that it pools more than it actually does.

Note   Most of the functionality of Microsoft Transaction Server has been incorporated into Windows Component Services for Microsoft Windows 2000 and XP. References to Microsoft Transaction Server in this article may not reflect the equivalent behavior with Windows Component Services. If you are using Windows 2000 or XP, see your operating system documentation for more information.

By default, service components are enabled for all Microsoft OLE DB providers if the provider is invoked by IDataInitialize or IDBPromptInitialize and if the provider is marked to work with pooling by using the OLEDB_Services registry key.

You can control pooling from your application in the following three ways:

• You can disable pooling for an individual provider through the registry.
• If you write directly to the OLE DB API, you can control pooling through the properties you set when connecting to the database.
• You can control pooling from your connection string.

The time-out value is set to 60 seconds; this value cannot be configured in OLE DB resource pooling prior to the release of MDAC 2.5.

Retry Wait works much differently in resource pooling than in ODBC connection pooling. After the server has been determined to be unavailable, resource pooling blocks the pool. The next retry for a valid connection on the server occurs after one minute. If this attempt fails, the next retry occurs after two minutes, and again after five minutes. Thereafter, the retry occurs every five minutes until a valid connection is returned.

To determine whether individual core components can be invoked to satisfy extended functionality requested by the consumer, OLE DB compares the properties specified by the consumer to those supported by the provider. Table 2 lists values that can exist in the OLEDB_Services registry entry, which should be created during installation of the provider.

Table 2. Setting OLE DB_Services Registry Entry Under Provider's CLSID

Default services enabled DWORD value

All services (the default)                                     0xffffffff

All services except pooling                                  0xfffffffe

All services except pooling and auto-enlistment     0xfffffffc

All services except client cursor                           0xfffffffb

All services except client cursor and pooling         0xfffffffa

No services                                                       0x00000000

No aggregation, all services disabled                  No OLEDB_Services registry entry

With the release of MDAC 2.5, you can configure both connection time-out and the Retry Wait values by using the registry.

When set under a provider's CLSID entry in the registry, the SPTimeout value controls the length of time, in seconds, that unused sessions are held in the pool. SPTimeout should be entered as a DWORD value under HKEY_CLASSES_ROOT/CLSID/ClassID where ClassID is the CLSID of the OLE DB provider. The default SPTimeout value is 60 seconds.

The Retry Wait value controls the length of time, in seconds, to wait between connection attempts. It is entered as a DWORD value under HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/DataAccess/Session Pooling. The default SPTimeout value is 60 seconds.

If you write directly to the OLE DB API, you can set the DBPROP_INIT_OLEDBSERVICES property to enable or disable various core components, including OLE DB resource pooling. There is no way to configure connection time-out or Retry Wait programmatically in your application except by manipulating the registry entries directly. It is best not to have any services or applications using OLE DB when you make these changes, or the effects might not show up right away.

Table 3 lists values and the services enabled or disabled by the value settings. Code Example 2 is an excerpt of code that uses resource pooling in an OLE DB consumer.

Table 3. Setting OLE DB Services by Using the DBPROP_INIT_OLEDBSERVICES Property

Services enabled Property value

All services                                                       DBPROPVAL_OS_ENABLEALL

All services except pooling                                 (DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_RESOURCEPOOLING)

All services except pooling and auto-enlistment   (DBPROPVAL_OS_ENABLEALL
& ~DBPROPVAL_OS_RESOURCEPOOLING & ~DBPROPVAL_OS_TXNENLISTMENT)

All services except client cursor                          (DBPROPVAL_OS_ENABLEALL
& ~DBPROPVAL_OS_CLIENTCURSOR)

All services except client cursor and pooling        (DBPROPVAL_OS_ENABLEALL
& ~DBPROPVAL_OS_RESOURCEPOOLING & ~DBPROPVAL_OS_CLIENTCURSOR)

No services                                                    ~DBPROPVAL_OS_ENABLEALL

Code Example 2: OLE DB Consumer Code Using Pooling

long OLEDBConnect( char   *lpszInitString,
                   bool    bUsePooling,         // Flag to set pooling
                   ULONG   nCount )
{
   IDataInitialize   *pIDataInitialize = NULL;
   IDBProperties     *pDBProperties    = NULL;
   IDBInitialize     *pIDBInit         = NULL;
   HRESULT            hr               = S_OK;
   WCHAR              wszInitString[1024];

   AtoU( lpszInitString, &wszInitString[0], 1024 );
   hr = CoCreateInstance(CLSID_MSDAINITIALIZE,
                         NULL,
                         CLSCTX_INPROC_SERVER,
                         IID_IDataInitialize,
                         (void**)&pIDataInitialize);
   for (ULONG i=0;i<nCount && SUCCEEDED( hr ); i++)
   {
      hr=pIDataInitialize->GetDataSource(NULL,
                                         CLSCTX_INPROC_SERVER,
                                         wszInitString,
                                         IID_IDBInitialize,
                                         (IUnknown**)&pIDBInit);
      if (pIDBInit)
         hr=pIDBInit->Initialize();
      if ( bUsePooling == false )
         if (pIDBInit)
            pIDBInit->Uninitialize();   // Disables Pooling(!)
      if (pIDBInit) pIDBInit->Release();   
         pIDBInit = NULL;
   }
   if (pIDataInitialize)
      pIDataInitialize->Release();
   return 0;
}

Note   One of the key points to note in the preceding code is that if you call IDBInitialize::Uninitialize, you will turn off pooling! To release a connection, use IDBInitialize::Release instead.

The OLE DB property DBPROP_INIT_OLEDBSERVICES maps directly to a connection string attribute, OLE DB Services, as shown in Table 4. Code Example 3 demonstrates this.

Table 4. Setting OLE DB Services by Using ADO Connection String Attributes

Services enabled Value in connection string

All services (the default)                                   "OLE DB Services = -1;"

All services except pooling                                "OLE DB Services = -2;"

All services except pooling and auto-enlistment   "OLE DB Services = -4;"

All services except client cursor                         "OLE DB Services = -5;"

All services except client cursor and pooling       "OLE DB Services = -6;"

No services                                                    "OLE DB Services = 0;"

Code Example 3: ADO Consumer Code Using Pooling

'This will take advantage of resource pooling.
Dim i As Integer
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset

cnn.Open "DSN=LocalServer;UID=MyUserName;PWD=MyPassword; " & _
         "OLE DB Services=-1"
For i = 1 To 100
      Set rst = New ADODB.Recordset
      rst.Open "SELECT * FROM Authors", cnn
      Set rst = Nothing
Next i

cnn.Close
Set cnn = Nothing

Resource pooling can be enabled in several ways:

• Automatically, when a consumer accesses a provider from within Microsoft Transaction Server or Internet Information Services 4.0 or later.
• For an OLE DB-based consumer, by using the IDataInitialize or IDBPromptInitialize interface. If you use IDataInitialize::CreateDBInstance, you can enable or disable services components by using the DBPROP_INIT_OLEDBSERVICES property.
• For an ADO-based consumer, by keeping one open instance of a Connection object for each unique user and using the OLEDB_SERVICES connection string attribute to enable or disable pooling. By default, ADO attempts to use pooling, but if you do not keep at least one instance of a Connection object open for each user, there will not be a persistent pool available to your application. (However, Microsoft Transaction Server keeps pools persistent as long as the connections in them have not been released and have not eventually timed out.)

Note   Pooling will not be enabled if you call CoCreateInstance directly on the CLSID of the data provider.

In OLE DB resource pooling, the following formula determines the number of pools (where N is the number of pools, P is the number of processors, and C is the number of distinct sets of connection attributes on the system):

N = (P + 1) * C

Using this formula, OLE DB resource pooling eliminates lock contentions on the pools.

Note   P+1 is a default value which can be overridden by setting the following registry key:
CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\Holders.

OLE DB resource pooling keeps a map of which pools contain which users. From there, it is possible to jump to the right pool and start looking for an available connection. With OLE DB resource pooling, a given pool contains connections only for a single provider with a specific set of connection attributes. In other words, each pool contains only connections to one set of credentials and one data store. As a result, a new pool will be created for each user if a user is connecting using Windows Authentication.

When developing an application using either ADO or native OLE DB, the following tips will help you to enable resource pooling or to avoid inadvertently disabling it. These rules apply whether you use ADO, OLE DB consumer templates, or native OLE DB code.

• The ADO Connection object implicitly uses IDataInitialize. However, this means your application needs to keep at least one instance of a Connection object instantiated for each unique user—at all times. Otherwise, the pool will be destroyed when the last Connection object for that string is closed. (The exception to this is if you use Microsoft Transaction Server. In this case, the pool is destroyed only if all of the connections in the pool have been closed by client applications and are allowed to time out.)

  Note   If you open a Connection object, remember to close it. Do not rely on garbage collection to implicitly close your connections.

• By avoiding connection creep (discussed in section "Connection Creep and Effective Server Tracing" later in this article), you can also help your application use pooling.

• Use IDataInitialize or IDBPromptInitialize to create an instance of your data provider. As long as one instance of either interface exists, a pool is available to your application. Otherwise, the pool will be destroyed.
• Do not request provider-specific interfaces prior to connecting.
• Do not request prompting.
• Do not use IDBInitialize::Uninitialize. Use IDBInitialize::Release instead. If you call Uninitialize, the connection you were using will be flushed from the pool. This is because the user can change the properties of the connection after calling Uninitialize.
• Do not release your last instance of IDataInitialize or IDBPromptInitialize while pooling is needed by your application.
• Use only one resource per connection.
• By avoiding connection creep (discussed below), you also help your application use pooling.

As mentioned above, ADO attempts to use or enable services implicitly by referencing the OLEDB_Services registry value when a connection is established. However, some older providers may not be able to support services. Simply creating this registry entry is not enough to enable services for your application. The data provider must support certain features or services will not work. Attempting to use services with a data provider that does not offer this functionality can create unexpected behavior in your application. Following is a brief list of the functionality a data provider must support for your consumer application to utilize services:

• The provider must support aggregation of all objects.
• Providers must support the free-threaded model or, at a minimum, the rental-threaded model. Services will determine the data provider's thread model by using the DBPROP_DSOTHREADMODEL property.
• If the provider has a global connection state that may change while the data source object is in an initialized state, it should support the DBPROP_RESETDATASOURCE property.
• Providers that connect to a remote database and can detect whether that connection has been lost should support the DBPROP_CONNECTIONSTATUS property. This allows pooling to detect dead connections and to ensure that they are not returned to the pool.
• Providers should support both pooling and transaction enlistment by using the DBPROP_INIT_OLEDBSERVICES property. The values used with this property also correspond to the OLEDB_Services registry key.

For more information, consult either the OLE DB Readme file that shipped with the Data Access 2.0 SDK or the OLE DB Services section in the documentation with version 2.1 and later of the SDK.

• Anonymous
  May 17, 2008
  PingBack from http://doslinux.com/2008/05/17/ole-db-resource-pooling/