ASP.NET data caching design

I have method in my BLL that interacts with the database and retrieves data based on the defined criteria.

The returned data is a collection of FAQ objects which is defined as follows:

FAQID, FAQContent, AnswerContent

I would like to cache the returned data to minimize the DB interaction.

Now, based on the user selected option, I have to return either of the below:

• ShowAll: all data.
• ShowAnsweredOnly: faqList.Where(Answercontent != null)
• ShowUnansweredOnly: faqList.Where(AnswerContent != null)

My Question:

Should开发者_如何学Python I only cache all data returned from DB (e.g. FAQ_ALL) and filter other faqList modes from cache (= interacting with DB just once and filter the data from the cache item for the other two modes)? Or should I have 3 cache items: FAQ_ALL, FAQ_ANSWERED and FAQ_UNANSWERED (=interacting with database for each mode [3 times]) and return the cache item for each mode?

I'd be pleased if anyone tells me about pros/cons of each approach.

Food for thought.

• How many records are you caching, how big are the tables?
• How much mid-tier resources can be reserved for caching?
• How many of each type data exists?
  • How fast filtering on the client side will be?
• How often does the data change?
  • how often is it changed by the same application instance?
  • how often is it changed by other applications or server side jobs?
• What is your cache invalidation policy?
• What happens if you return stale data?
• Can you/Should you leverage active cache invalidation, like SqlDependency or LinqToCache?

If the dataset is large then filtering on the client side will be slow and you'll need to cache two separate results (no need for a third if ALL is the union of the other two). If the data changes often then caching will return stale items frequently w/o a proactive cache invalidation in place. Active cache invalidation is achievable in the mid-tier if you control all the updates paths and there is only one mid-tier instance application, but becomes near really hard if one of those prerequisites is not satisfied.

It basically depends how volatile the data is, how much of it there is, and how often it's accessed.

For example, if the answered data didn't change much then you'd be safe caching that for a while; but if the unanswered data changed a lot (and more often) then your caching needs might be different. If this was the case it's unlikely that caching it as one dataset will be the best option.

It's not all bad though - if the discrepancy isn't too huge then you might be ok cachnig the lot.

The other point to think about is how the data is related. If the FAQ items toggle between answered and unanswered then it'd make sense to cache the base data as one - otherwise the items would be split where you wanted it together.

Alternatively, work with the data in-memory and treat the database as an add-on...

What do I mean? Well, typically the user will hit "save" this will invoke code which saves to the DB; when the next user comes along they will invoke a call which gets the data out of the DB. In terms of design the DB is a first class citizen, everything has to go through it before anyone else gets a look in. The alternative is to base the design around data which is held in-memory (by the BLL) and then saved (perhaps asynchronously) to the DB. This removes the DB as a bottleneck but gives you a new set of problems - like what happens if the database connection goes down or the server dies with data only in-memory?

Pros and Cons

• Getting all the data in one call might be faster (by making less calls).
• Getting all the data at once if it's related makes sense.
• Granularity: data that is related and has a similar "cachability" can be cached together, otherwise you might want to keep them in separate cache partitions.