System Cache and expiration

[tshad]

I was discussing a question in the Sql Server group on how to handle Stored
Procedure and how many to have as well as whether or not to read a whole
record into my page if I only need a few fields out of it.

The reason being that I could have 15 pages all wanting different types of
data and this would require 15 different SPs. Or I could just have one
stored procedure that reads the whole record and always read that one and
grab what you need. The problem here might be networking issues on sending
large amounts of data when just a few fields will do.

The question was do I create another SP each time I create a new page that
needs an extra field from the other SPs, or just read the whole thing.

One of the solutions was to read the whole thing into a class and cache the
class (session), but the problem was how do I handle changes (updates,
deletes) that may hit that record from another users.

His other solutions was:

If there are concurrency issues, then don't use Session: use the system
cache (or Application). With the system cache, you can set an expiration
time on the objects stored there, as well as including code to flush the
class from the cache when an update occurs.

I was wondering how I would do this, to have an update trigger the deletion
or reload of an object in the system Cache.

Also, is the system Cache the same as the Application?

Thanks,

Tom

 

[Marina]

Maybe as input your stored procedure can accept a list of columns the user
wants, and only return those.

 

[Sahil Malik [MVP]]

Tom,

In .NET 2.0 you can use SqlDependency to trigger the clean out of expired
cache objects.
In .NET 1.1, you would need a trigger to touch a file on the file system,
and use FileDependency instead.

As far as the rest of your question goes, it is hard to comment on it
without seeing the exact table structure, and why just a record, why not the
entire table (in short I'm a bit unclear on the rest to pass any comment,
though I'm guessing a better solution may exist).

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[tshad]

Maybe as input your stored procedure can accept a list of columns the user
wants, and only return those.

Is that Dynamic Sql?

Tom