ADO.Net would be nice...  Can't go too much into the application as
I'm bound by the usual terms, but we aren't using MS SQL.

Here is the short story, though we can use connection pooling, we
still have a large number of open connections at any one time and
since all transactions are stateless, we created a singleton so we can
ride the same connection object over and over again.  It reduces the
connection prep time a little, and we have seen about a 20% increase
over native pooling.  The pool is also dynamic so connections that are
more than 5 minutes old get removed and if there are no available
connections, new ones get created.  You know, old school connection
pooling... :)

We have thought about output caching, but the problem is the content
is designed into the page.  We are trying to break them into smaller
data islands, but this won't be for a while.  So for the average user,
they might have 6 to 10 islands of data.  Let's say there are about
1000 variants of the data at any point in time.  Currently, after we
identify which data the user gets, we retrieve it from the database.
This is where we are getting the hit at.  The business unit has
accepted 10 minute delays from the time the data changes to the
presentation.  So my goal is to reduce the overall database load while
the other team starts to refactor this application (probably
incorporating AJAX, or some other technology).  In the mean time, I'm
approaching from reducing the data calls.

The data request load is still the biggest problem.  We could, in
theory, serialize the datatables, or possibly save it into application
scope, but this also runs into some locking issue and also the issue
of updating, but this will increase the load on the web farm.  So the
idea was to keep the DataTable in memory and just DataTable.Copy() (I
used clone earlier by mistake) when an ASP page calls for it.

Now I have create a custom class since the original post, which does
the simple DataTable caching, which will see if a macthing SQL
statement exists, and if it does then send it to the caller, otherwise
retrieve it.  The query itself takes about 300ms, but once it's cached
I can iterate it about 7k times per second on my workstation
(including locking, etc -- and only hitting the database the first
time).  It's also set to expire (and auto refresh the content).  I'm
just looking for more elegant solutions and variations.  I'm still
looking into the memory implications though.