Re: ODBC/OLE DB Connection Pool

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 11/26/04

  • Next message: Bob Barrows [MVP]: "Re: ODBC/OLE DB Connection Pool" 
    Date: Fri, 26 Nov 2004 13:11:47 -0500

    Erland Sommarskog wrote:
    > Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
    >> If you read Stuart's post carefully, you will see that you do not
    >> have to do anything to turn Session pooling on. It's on by default.
    >> You have to do extra work to turn it off.
    >
    > Well, in VB it as easy as:
    >
    > Publlc Function Button1_Click () As
    > Dim cnn As new ADODB.Connection
    > ...
    > Set cnn = Nothing
    > End Function
    >
    > And if there is no global ADODB.Connection, there will be no pooling.
    > (Which Stuart also discussed in his post.)
    >
    > If the above is possible to do in ASP, I don't know.

    Oh wait, It HAS been a while since I read this article. I now see this
    paragraph:

    **********************************************************************************
    Tips for ADO Users
    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.
    *************************************************************************

    I do question whether this means that the pool won't be recreated the next
    time a connection is opened

    And check out the exception noted above regarding the use of MTS. In ASP, if
    you use Server.CreateObject, you are forcing the use of MTS. Using
    vbscript's CreateObject will not cause MTS to be used and you may lose the

    And this one (I've added numbers):
    ****************************************************************************
    Unintended Disabling of Resource Pooling
    Resource pooling can be inadvertently disabled for your application. The
    persistence and behavior of resource pooling depend on several conditions
    that occur with a given set of user authentication criteria, such as the
    following:

    1. A given resource pool is specific to the connection attributes of that
    set of authentication criteria.
    Do not call IDBInitialize::Uninitialize, which implicitly disables pooling.
    Instead, use IDBInitialize::Release. While both IDBInitialize::Uninitialize
    and IDBInitialize::Release close the connection, calling Uninitialize will
    result in the pool being destroyed. (This is not an issue for ADO
    developers.)

    2. The data source object must be created by using IDataInitialize or
    IDBPromptInitialize, and not by CoCreateInstance directly on the OLE DB data
    provider. For pools to exist for a given set of connection attributes,
    resource pooling requires at least one instance of an OLE DB data source
    object created per unique set of connection properties.

    3. If using ADO, at least one ADO Connection object must remain open for
    each set of user authentication credentials. Although ADO cannot call
    Uninitialize, letting go of all ADO Connection objects for a given set of
    connection attributes accomplishes the same thing-that is, it releases the
    last instance of IDataInitialize.
    ********************************************************************************

    The first two are not relevant in ASP. The last one is also not relevant:
    ADO is not free-threaded, so it is not recommended that a single global
    connection be kept open for the application as this will serialize all
    communications with the database.

    Bob Barrows 

    -- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
  • Next message: Bob Barrows [MVP]: "Re: ODBC/OLE DB Connection Pool"