Re: ODBC/OLE DB Connection Pool

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

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"
Loading