Keeping a database connection available

[Tve]

Hi,

I started a tread about recordsets an I would like to thank link9 for his (quick) answer.

This is similar question:

Can I also put a connection object in a session, like for recordsets? I'm connecting to an Oracle database, and the connecting to the data source requires some time (not much, but time anyway.)

Are there disadvantages to doing this?

Thanks,

Thierry

 

[ChrisQuick]

There are MAJOR disadvantages to putting COM Objects in sessions. Charles Carroll has an excellent article on his site explaining this at:

http://www.learn.asp/learn/nodbsession.asp

As a rule, never place COM objects in Sessions or Applications collections.
cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[0606]

Hi,

Yes, You can keep your connection state in session level.

Cheers,
Sri...

 

[phuctran]

Yes you can do that but you should not.
you should open connection, get information, and close connection asap.

 

[bushey]

Can you explain why it's better to open connections and close them rather than keeping a connection variable open throughout the time the user is logged in and using an application?

I support an intranet application at a medical center, and the call center intake workers are accessing the database for retrieval, inserts, deletes and updates all throughout their eight-hour shifts. There's a lot of "overhead" in opening database connections on every page, and we're wondering if we can keep the number of connections being opened down to a minimum.

Thanks in advance for your feedback/advice.

Adele Miller
Application Engineer

 

[glendsj]

you don't add any overhead, you actually reduce it.
The object is instatiated, used and destroyed faster than you can blink. Using a session variable causes the memorary use to be constant, and once a session ends by default the connection is lost. I work a lot in Oracle, and using one open connection, that connection manager has to control takes a ton more time than following good ASP coding and opening, using, closing, destroying.

 

[multiplex77]

Thanks glendsj for clarifying bushey's qn. I had that same question.

I'm currently running a huge ASP application with a SQL Server database. I've created 2 include files: dbConnection.asp and dbCleanup.asp. THe first file contains all the instantiation of connection and recordset objects, as well as the assignment of the connection string, etc. The 2nd file just sets all the objects to Nothing.

I'm including these two files at the Top and Bottom of every ASP page in my application.

Is this the recommended method (according to what glendsj just recommended)? Is it generating any unnecessary overhead?

Are there any other tips to recommend for improving the efficiency of the database connections in a large ASP application?

Thanks much for the help!