List Members,



Trying to gather some information on Connection Pooling.

So far I've been working under the assumption that it's reasonable to 

adopt the approach of "open and close the connection on every page", as 

in :



    ' Create the DB Connection

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

    Set objRS = Server.CreateObject("ADODB.Recordset")

    ' Set the Cursor Location

    objConn.CursorLocation = adUseClientBatch

    strConnect = "DSN=DBDSN;"          

    objConn.Open strConnect 

    sSQL="select col1, col2, FROM table ORDER BY col1"  

    ' Set the CursorType and LockType to be used       

    objRS.cursortype = adOpenForwardOnly 

    objRS.locktype = adLockReadOnly

    ' Open and populate the RecordSet  

    objRS.Open sSQL, objConn



    .... FORMAT DATA INTO HTML TABLE ....



    ' Close DB objects and free variables

    objRS.Close

    Set objRS = Nothing

    objConn.Close

    Set objConn = Nothing



but I am now beginning to wonder about how Connection Pooling works.

I'm currently using ODBC to connect to the database, so I had a

couple of questions :



   1. How does Connection Pooling actually work ? 

   2. Will Connection Pooling be enabled by default ?

   3. Is there a tool which allows you to monitor the Connections used?

   4. Is it good practice to create&close the Connection object on each

      ASP page ?

   5. Will creating the Connection object actually create a new Connection

      or reuse a Connection from the pool ?

   6. Is putting the connection in a session or application variable  

      advisable ?



Sorry for the number of questions, but I'd appreciate any pointers

regarding Connection Pooling and the above points.



Thanks.



Ray

Ray,



Q1)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html

/pooling2.asp



Q2)

see above



Q3)

Yes/No. Depends on the database etc. In SQL Server you can use the Profiler



Q4)

Well, you need to create it...and every object you create your should set to

nothing (see article above). Basically everything you .Open you .Close, and

everything you Set, you Set = Nothing. That is good programming practise



Q5)

Depends - usually it does. There was an article out there that said

connection/resource pooling is not enabled for MS Access/Jet



Q6)

No, it is a -bad- idea. Someone once did tell me you could register the

free-threaded ADO .dll if you weren't using Jet at all, and keep a recordset

in a session object...

Connection objects should not be stored in session/application vars. If you

stored it in a session var then you are creating a new conneciton for each

new user. Connection pooling will be negated. You will rapidly use up your

connection lisences/memory if you're on a busy server. If you put it into an

application var then every user will use the same connection leading to big

serialisation problems.



Cheers

Ken



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

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

From: "Ray Murphy" <raymondmurphy@c...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, June 27, 2001 11:19 AM

Subject: [asp_databases] Some Connection Pooling Questions ...





: List Members,

:

: Trying to gather some information on Connection Pooling.

: So far I've been working under the assumption that it's reasonable to

: adopt the approach of "open and close the connection on every page", as

: in :

:

:     ' Create the DB Connection

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

:     Set objRS = Server.CreateObject("ADODB.Recordset")

:     ' Set the Cursor Location

:     objConn.CursorLocation = adUseClientBatch

:     strConnect = "DSN=DBDSN;"

:     objConn.Open strConnect

:     sSQL="select col1, col2, FROM table ORDER BY col1"

:     ' Set the CursorType and LockType to be used

:     objRS.cursortype = adOpenForwardOnly

:     objRS.locktype = adLockReadOnly

:     ' Open and populate the RecordSet

:     objRS.Open sSQL, objConn

:

:     .... FORMAT DATA INTO HTML TABLE ....

:

:     ' Close DB objects and free variables

:     objRS.Close

:     Set objRS = Nothing

:     objConn.Close

:     Set objConn = Nothing

:

: but I am now beginning to wonder about how Connection Pooling works.

: I'm currently using ODBC to connect to the database, so I had a

: couple of questions :

:

:    1. How does Connection Pooling actually work ?

:    2. Will Connection Pooling be enabled by default ?

:    3. Is there a tool which allows you to monitor the Connections used?

:    4. Is it good practice to create&close the Connection object on each

:       ASP page ?

:    5. Will creating the Connection object actually create a new Connection

:       or reuse a Connection from the pool ?

:    6. Is putting the connection in a session or application variable

:       advisable ?

:

: Sorry for the number of questions, but I'd appreciate any pointers

: regarding Connection Pooling and the above points.

:

: Thanks.

:

: Ray

Ken,



Thanks a lot for the URL to the MSDN article, which explained a lot.

The clarification on not storing Connection objects in session/application 

variables was particularly interesting.



Thanks.



Ray

> Ray,

> 

> Q1)

> http://msdn.microsoft.com/library/default.asp?url=/library/en-

us/dnmdac/html

> /pooling2.asp

> 

> Q2)

> see above

> 

> Q3)

> Yes/No. Depends on the database etc. In SQL Server you can use the 

Profiler

> 

> Q4)

> Well, you need to create it...and every object you create your should 

set to

> nothing (see article above). Basically everything you .Open you .Close, 

and

> everything you Set, you Set = Nothing. That is good programming practise

> 

> Q5)

> Depends - usually it does. There was an article out there that said

> connection/resource pooling is not enabled for MS Access/Jet

> 

> Q6)

> No, it is a -bad- idea. Someone once did tell me you could register the

> free-threaded ADO .dll if you weren't using Jet at all, and keep a 

recordset

> in a session object...

> Connection objects should not be stored in session/application vars. If 

you

> stored it in a session var then you are creating a new conneciton for 

each

> new user. Connection pooling will be negated. You will rapidly use up 

your

> connection lisences/memory if you're on a busy server. If you put it 

into an

> application var then every user will use the same connection leading to 

big

> serialisation problems.

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

> From: "Ray Murphy" <raymondmurphy@c...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Wednesday, June 27, 2001 11:19 AM

> Subject: [asp_databases] Some Connection Pooling Questions ...

> 

> 

> : List Members,

> :

> : Trying to gather some information on Connection Pooling.

> : So far I've been working under the assumption that it's reasonable to

> : adopt the approach of "open and close the connection on every page", as

> : in :

> :

> :     ' Create the DB Connection

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

> :     Set objRS = Server.CreateObject("ADODB.Recordset")

> :     ' Set the Cursor Location

> :     objConn.CursorLocation = adUseClientBatch

> :     strConnect = "DSN=DBDSN;"

> :     objConn.Open strConnect

> :     sSQL="select col1, col2, FROM table ORDER BY col1"

> :     ' Set the CursorType and LockType to be used

> :     objRS.cursortype = adOpenForwardOnly

> :     objRS.locktype = adLockReadOnly

> :     ' Open and populate the RecordSet

> :     objRS.Open sSQL, objConn

> :

> :     .... FORMAT DATA INTO HTML TABLE ....

> :

> :     ' Close DB objects and free variables

> :     objRS.Close

> :     Set objRS = Nothing

> :     objConn.Close

> :     Set objConn = Nothing

> :

> : but I am now beginning to wonder about how Connection Pooling works.

> : I'm currently using ODBC to connect to the database, so I had a

> : couple of questions :

> :

> :    1. How does Connection Pooling actually work ?

> :    2. Will Connection Pooling be enabled by default ?

> :    3. Is there a tool which allows you to monitor the Connections used?

> :    4. Is it good practice to create&close the Connection object on each

> :       ASP page ?

> :    5. Will creating the Connection object actually create a new 

Connection

> :       or reuse a Connection from the pool ?

> :    6. Is putting the connection in a session or application variable

> :       advisable ?

> :

> : Sorry for the number of questions, but I'd appreciate any pointers

> : regarding Connection Pooling and the above points.

> :

> : Thanks.

> :

> : Ray

> 

>