The pooling timeout got me interested in checking it further. I found an
article in MSDN Library's Technical Articles section titled "Pooling in
Microsoft Data Access Components" that addresses this. In summary it says
that you can change the timeout (which defaults to 60 seconds) by modifying
registry values. There are two keys; one for ODBC and one for OLEDB. If
anyone wants, I'll look up the relevant keys.

-----Original Message-----
Subject: Re: Performance - Calling StoredProc with ADO in Cla     ss
From: "Jeff Mason" <jeffm@u...>
Date: Thu, 28 Jun 2001 12:23:00 -0400
X-Message-Number: 16

The reason an ADO connection will 'open' much faster the 2nd and subsequent
times is that OLEDB doesn't actually destroy the connection when it is
closed; instead the connection object is placed in a pool.  If another
request is made to create a connection object *with the same properties* as
a connection object already in the pool (e.g. it has to essentially have the
same connection string), the existing object is used instead of another
object being created.  A connection object does not remain in the pool
forever - it will get "stale" after a timeout period (which I do not believe
is changeable) and will be destroyed.

Given your example, yes there will be a performance 'hit' if you wait too
long, since the pooled connection object will have gone stale, so it will
have to be (re)created.  On the other hand, the performance hit you are
experienced may have more to do with caches being flushed than in creating a
connection object...

This doesn't change the fact that keeping a connection open for a 'long'
time does not scale well, which was the point of my post.  If you have
hundreds of users, that approach will overwhelm the server and you will
experience horrible performance.  It works fine if the number of users is
relatively small - a dozen or two or so - it depends a lot on the
configuration and load on your network and server.

--
Jeff Mason			Custom Apps, Inc.
jeff@c...

-----Original Message-----
From: Tim Mccurdy [mailto:tmccurdy@c...]
Sent: Thursday, June 28, 2001 10:34 AM
To: professional vb
Subject: [pro_vb] Re: Performance - Calling StoredProc with ADO in Cla
ss


Also, due to Connection pooling I believe, an ADO Connection (once opened
initially) that is closed will open much faster the 2nd time and so on.  I
believe there is a Time Out on the Server though that after a certain amount
of time, your Connection will then be expensive once again when trying to
open it after closing.

For example.  I create a Form that opens the DB retrieves the Recordset and
Disconnects.  I edit some Records, and on the UnLoad Event of the Form, I
open the DB and Commit the changes.  The 2nd time the Connection gets
opened, there is no performance hit.

However, if I open the smae Form, and edit some Records, then go to lunch.
When I come back and Close the Form to Commit the changes, the Connection
will then be expensive once again.  Ha!

Am I correct in this assumption?  I have tested Connections a lot and this
seems to be the norm for me (running SQL Server 2000).