Thanks Hal,



But it doesn't really answer the question I posed. I already know the best

pratices when using connection objects. Let me rephrase the question....



Does pooling still work if you open and close connections on each page but

keep the connection object instantiated throughout the application on a

session level. Or does each page need its own instance of the object

instantiated. i.e. is it the connection made by the object or the object

itself that is cached?



I'd appreciate any answers that involve some form of reasoning.



Thanks



Simon Ryan



----- Original Message -----

From: "ASP Database Setup digest" <asp_database_setup@p...>

To: "asp_database_setup digest recipients" <asp_database_setup@p...>

Sent: Saturday, May 26, 2001 12:00 AM

Subject: asp_database_setup digest: May 25, 2001





> ASP_DATABASE_SETUP Digest for Friday, May 25, 2001.

>

> 1. Connection pooling

> 2. RE: Connection pooling

>

> ----------------------------------------------------------------------

>

> Subject: Connection pooling

> From: "simon ryan" <sjr100@c...>

> Date: Fri, 25 May 2001 11:56:58

> X-Message-Number: 1

>

> does anybody now if conection pooling still works if you cache a copy of

> the connection object at session level then open and close it on each

page.

> Or is anyone aware of any other issues or implications of doing this?

> DBMS is Oracle.

> Thanks in advance for any contribution

> ----------------------------------------------------------------------

>

> Subject: RE: Connection pooling

> From: Hal Levy <hal.levy@s...>

> Date: Fri, 25 May 2001 09:55:35 -0400

> X-Message-Number: 2

>

> _NEVER_ use a connection object on the session level.

> _NEVER_ use a connection object on the Application level.

> _BETTER_ open a connection object right before you need it on a page and

> destroy it as soon as you are done with it.

> _BEST_ Do no dB access from ASP. Write COM objects to do your data access.

>

>

>

> Hal Levy

> StarMedia Network, Inc.

> Intranet Development Manager

>

> -----Original Message-----

> From: simon ryan [mailto:sjr100@c...]

> Sent: Friday, May 25, 2001 7:57 AM

> To: ASP Database Setup

> Subject: [asp_database_setup] Connection pooling

>

>

> does anybody now if conection pooling still works if you cache a copy of

> the connection object at session level then open and close it on each

page.

> Or is anyone aware of any other issues or implications of doing this?

> DBMS is Oracle.

> Thanks in advance for any contribution

Despite your rudeness, I will continue to endeavor to help you.



"Does pooling still work if you open and close connections on each page but

keep the connection object instantiated throughout the application on a

session level."



let me see if I can interpret.



you want to:

set MyConn = server.createobject..

set Session("MyConn") = MyConn



and then on each page you want to:

set myConn = session("MyConn")

myConn.open

myConn.close



If this is a correct understanding- The object is ALWAYS there, destroying

server performance. But this isn't what you care about.



If I do understand the question correctly, my answer is "I have no idea". I

don't know why anyone would ever do this and I don't know of anyone ever

testing this.



You could, however, test this. (I am thinking on the fly here- this might

not really tell you anything)



On each page ask the connection to tell your it's ID... then write it out...

Have a number of users connect. See if the connection ID's get passed around

with the users or if you always get the same connection. If you always get

the same connection- then it's unlikely pooling is taking place. Make sure

that it's not just different every time- but the SAME connections are being

used by different users.





I say again- if you are concerned about the performance benefits of

connection pooling, then be MORE concerned with the connection at the

session level. It does you more damage than the lack of connection pooling.





Hal Levy

StarMedia Network, Inc.

Intranet Development Manager



-----Original Message-----

From: simon Ryan [mailto:sjr100@c...]

Sent: Tuesday, May 29, 2001 3:41 AM

To: ASP Database Setup

Subject: [asp_database_setup] Re: asp_database_setup digest: May 25,

2001





Thanks Hal,



But it doesn't really answer the question I posed. I already know the best

pratices when using connection objects. Let me rephrase the question....



Does pooling still work if you open and close connections on each page but

keep the connection object instantiated throughout the application on a

session level. Or does each page need its own instance of the object

instantiated. i.e. is it the connection made by the object or the object

itself that is cached?



I'd appreciate any answers that involve some form of reasoning.



Thanks



Simon Ryan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

:

: Despite your rudeness, I will continue to endeavor to help you.

:

: "Does pooling still work if you open and close connections on each page

but

: keep the connection object instantiated throughout the application on a

: session level."



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Hal,



I don't think Simon was being rude. He posted a question. You replied with

an ALL CAPS rant, which is fair enough (it is free support here - you can

answer how you see fit)...except you didn't answer his question, which he

politely pointed out. That doesn't constitute rudeness in my books.



Simon,



In answer to your question - this is my take on the matter, but I could be

wrong. As Hal did point out, this isn't something that I've ever seen done,

and I don't really know why you'd want to do it that way.



That said...



Set objConn = Server.CreateObject("ADODB.Connection")



creates a proxy object. To the OLEDB consumer it appears that it has a

connection to the database. Any properties you set are cached. When you call

.Open, an attempt is made by the OLEDB Core Services to find a matching

connection in the connection or resource pool (ODBC or OLEDB). If one is

found, it is returned to the consumer. If not, a connection (and a new pool)

is created.

When you call .Close the connection is returned to the pool. So if you do

this:



Set objConn = Session("objConn")

objConn.Open

...

objConn.Close



Then you would not be disabling connection pooling in your pages (AFAIK).

That said, you are still carrying around an object reference in memory on

the server for each and every visitor you have...if someone has in-memory

cookies disabled, you'd be creating a new session on each and every page

that the user visited. That's a lot of session object references you're

carrying around.



The *only* reason I can think of that you'd want to do it like the above is

to cut down on the number of lines of code to write. That said, if you have

a generic functions library, you can just have a fncGetConn, and a

subADOClose to take take of this for you, eg:



' * * * * * * * * * * * * * * * * * * * * * * * * * * *

' Accepts objToClose as object reference

' Attempts to close the object

' Attempts to set the object to nothing

' No Error Handling

' * * * * * * * * * * * * * * * * * * * * * * * * * * *



Sub subADOClose( _

    ByRef objToClose _

    )



    On Error Resume Next

    Const Proc = "subADOClose"



    If objToClose.State = adStateOpen then

        objToClose.Close

    End If



    If isObject(objToClose) then

        Set objToClose = Nothing

    End If



End Sub ' subADOClose

' ...

' ...

Call subADOClose(objRS)

Call subADOClose(objConn)



HTH



Cheers

Ken