Preserving server resources

[JP SIngh]

Hi All

Our server recently has started to play up and hangs every 7-10 days.
Someone has suggested that the way we open database connections might be
using too many server resources.

Can someone please point out what would be the bext way of opening a
database connection.

What we presently have is a connection.asp file which has the following
code.

CONNECTION.ASP

set conn = Server.Createobject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "d:\mydatabases\Assets\Tracking.mdb"

We simply include this file on top of everypage that will need the database
connection however recently someone pointed out that we might be opening
connection unneccsarrily as the connection is being opened on every visit to
everypage but is not being closed.

Is this the correct approach?

Regards
Jas

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

The biggest issue is making sure connections get closed and disposed. Open
as late as possible and close as soon as you are finished. That is one of
the surest ways to improve perf with conns.

After close, issue this:

Set conn = Nothing

The connection object will still be pooled by ADO, but it will not be
flagged as unable to destroy once it no longer needs the pool.

NOTE: In most cases, ASP takes care of this nicely. There are instances,
however, that you can end up with a memory leak by not closing and
destroying connections. Better safe than sorry.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************