> I am designing a web application (currently in it's infancy stage)
> using superserver and I would like to know how I can keep a database
> cached for as long as possible. I'm not looking for server
> configuration tweaks at this time.
>
> The lifespan of my database connections will be extremely short,
> basically a web visitor will make a request (click on a link or
> submit a form) causing my web application to make a db connection,
> perform the required task (such as query, update or insert), and when
> finished, the connection is immediately disconnect.
>
> Unlike a desktop application, this constant connecting/disconnecting
> happens because there really is no "session state" for a web
> application. I can't just leave an active connection for every
> visitor for instance.
>
> On this newsgroup, I read a post by Ann Harrison suggesting that the
> way to retain cache is to keep an open connection to the database,
> just some dumb connection that does nothing. Her advise was in
> regards to a read only database, suggesting that garbage collection
> was not a problem since this particular database was not going to be
> updated, thus causing a need for garbage collection. She did warn
> that garbage collection is required for databases that are written to.
>
> Since my database will be constantly read from and updated, I am
> wondering if I can create a timer and have it connect/reconnect from
> the dummy connection every 2 hrs or so? Maybe leaving the dummy
> connection off for about 5 minutes in between?
>
> Any thoughts/suggestions welcome,

What about a connection pool? You can establish n* connections and
manage them through your pool. When a query needs to be run, simply
grab an active connection out of the pool. If you run out, create one
at that time.

Holding the connections open reduces the overheads associated with
establishing the connection / authentication etc and should keep the
database interesting to the server.

I would be very surprised if an active *connection* had anything to do
with garbage collection. A long running active *transaction* *is* the
issue.

Adam

* n connections is a number that is based on your expected load. It
must be high enough that you are covered 99% of the time, but not too
high that it wastes your server resources for no noticeable benefit.