Caching often-used SQL queries

[Shabam]

Suppose in a .net application a user sees his homepage, which includes many
elements the requires sql queries. Each user sees a different homepage
obviously. Executing these queries every time a user goes to his homepage
is obviously going to drain a lot of systems resources, especially since
most of the time it's the same.

What are some of the ways to make this efficient? Perhaps some sort of
caching mechanism, or even a temporary table that stores just the elements
needed for display in the homepage?

[Michael Giagnocavo [MVP]]

ASP.NET has a cache object built-in. So you could store your dataset in
there for 5 minutes (or whatever) and save a trip. Even better, if you can
move the common parts into a web user control (.ASCX), you can use output
caching and cache the entire HTML block (so no rendering of controls will
even occur).

-mike
MVP

"Shabam" <in...@pcconnect.net> wrote in message
news:FcudnYoJ9_y...@adelphia.com...

[Jerry Pisk]

The problem is that neither one of those are user (session) specific. You
need to enable sessions and store the data in the session context. Or you
can use cookies if there's not too much data.

But I don't think it's worth it, first of all, users do not request the home
page over and over. Second - you'd be caching so much data that it would
take up too much memory and cause a slowdown of everything on the system.
And if you tune your database server you'll spend much more time processing
the data and rendering the page (a step you wouldn't save by caching the
database results) that it's going to be pointless in trying to save a
fraction of your processing time querying the database.

Jerry

"Michael Giagnocavo [MVP]" <mggU...@Atrevido.net> wrote in message
news:Opd1tk8B...@TK2MSFTNGP11.phx.gbl...

[Michael Giagnocavo [MVP]]

They aren't session specific, but I'm guessing at least some of the data on
a user's homepage shares some data that could be cached. An alternative is
to use a cache key like "USERID-Key", and set it to very low priority. Then
those who hit refresh a few times (if this is indeed a performance
bottleneck) will get use of the cache.

-mike
MVP

"Jerry Pisk" <jerr...@hotmail.com> wrote in message
news:eLa0Q98B...@TK2MSFTNGP12.phx.gbl...