Asp.Net MVC best way to update cached table

Question

There are certain tables that get called often but updated rarely. One of these tables is Departments. So to save DB trips, I think it is ok to cache this table taking into consideration that the table has very small size. However, once you cached it an issue of keeping the table data fresh occurs. So what is the best way to determine that the table is dirty and therefore requires a reload and how that code should be invoked. I look for solution that will be scalable. So updating the cache on single right after inserting will not resolve the issue. If one machine inserted the record all other on the farm should get notified to reload the cache. I was thinking for calling corresponding web service from T-SQL but don't really like the idea of consuming recourses on sql server. So what are the best practices to resolve this type of problems. Thanks in advance Eddy

Answer 1

Have you tried using sql dependencies or cache dependencies? The library will pole the database every so often to see if the data has changed. An alternative is to use cache dependencies too. You can have a master cache object and have child caches depend on it. so if the master cache change the child caches will be updated.

Edit: If the above is not a solution you can easily use memcached.net -- wikipedia. Geared toward large sites but it is a solution for your problem.

Answer 2

There are some great distributed caching frameworks out there. Have a look at NCache and Velocity. NCache has some great features for keeping the cached data in sync between different cache nodes as well as the underlying database. But it comes at a price.