[thelist] Connection Inefficiency

[Ken Schaefer]

Hi,

This has been posted a few times - I know at least one time I've posted it in
response to one of *your* previous posts:

Pooling in the Microsoft Data Access Components
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/
pooling2.asp

ASP, and even the ADO components that you use in your pages don't know
anything about connection pools. So, you are correct that this might seem
"inefficient". 

But, underneath this is another layer - OLEDB (or ODBC on top of OLEDB).
OLEDB maintains a transparent pool of connections that are held open, and
when upper layer components (namely ADO) call for an open connection
(objConn.Open), OLEDB hands over a connection from the pool. When you dispose
of the connection (Set objConn = Nothing), OLEDB returns the connection to
the pool. 

You should *NOT* attempt to maintain your own persistent connections, or
maintain your own connection pool (unless you know exactly what you're doing
and why). In fact, one of the biggest scalability killers that I've seen in
ASP-based applications has been poor database code written by people who
think that it's a good idea to maintain their own connections in session or
application scope. This can result in severe serialisation issues and/or
database resource exhaustion.

Cheers
Ken


: -----Original Message-----
: From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On
: Behalf Of Rob Smith
: Subject: [thelist] Connection Inefficiency
: 
: Have you ever noticed that on each one of your pages... I don't know, maybe
: you code like this... you have some form of connection string. And if
you're
: a good coder, at the end of your pages, you close the connection.
: 
: That's a highly inefficient use of server resources if you ask me. Isn't
: there a cleaver way to detect whether or not a connection to the database
is
: open for your session, rather than on each page you open and close it?
: 
: It's probably starring me right in the face,
: 
: Rob Smith
: 
: p.s. scripting language is indifferent here...