Data Caching Stores In ASP.NET MVC

Question

Greetings fellow earthlings.

I'm researching methods of caching data retrieved from various database tables using ASP.NET MVC. We have a huge amount of data which is only updated once a day and from then on is static. This can vary but generally it's things like a list of users (employees), to departments, offices, locations we're in etc. etc.

My question is, how on earth do you retrieve the data in the first place? Where do you store it? (text file, or in actual code?) What determines whether the cache needs to be updated? I'd prefer an object-oriented approach so I can treat the rows of data as objects. I already have a model I've created by hand using LINQ in my MVC application. Additionally, how should I check if the cache is dirty? Should the database do something, or should the application do something? For example, when a row is updated should a database trigger go and do something to indicate that a change has been made (perhaps it could write to a text file?), or should my application quickly check to see if anything has changed? I realise there's many-a-method around, but is there a recommended one?

Take into account I have zero experience with caching, so be gentle ;)

Answer 1

Either hold a static object (Array, List, Hashmap and the like) with all the data items for each type of data (an array for employees, another for departments and so on), or use the server Cache and store those data containers within it using your own defined key. Using the Cache is pretty straight forward and most commonly used.

Using the Cache object also allows you to specify expiration (absolute or timed by the last touch on the key), and cache dependency (where you can reset cache items based on a FS file).

If necessary, more complex scenarios could be created (expiration by limiting cache size on a specific item for example).

Answer 2

Like others mentioned you should probably use Cache and invalidate the Cache with a dependeny on a table.

One thing you should ask yourself is how you need to query your data. If you need the power of sql for that, then the cache might get in your way. If you need to filter, page and sort the data you have to decide where you do it: in sql or by linq after you get the result from the cache. The answer depends mainly on the time you need to run a query for a resultset.