.Net MVC Controller force cache refresh depending on situation

Question

I have a controller action that returns the Average of a series of measurements from a group of tests. The controller basically gets the data-points from all the tests within the group specified and then returns the average of all the data points. Tests are added to the Test Group regularly.

I'd like to optimize my controller so that it caches the result (because what's the point of querying the database on every request if the data has not changed?) and rebuilds the cache only when a request is received and a new test has been added to the test group. My table structure (simplified) is below.

What's the best way to achieve this?

TEST_GROUP
------------
group_name
group_id

TEST
-------------
test_id
group_id
test_date

DATA
--------------
test_id
measurement_number
measurement

Is your SQL performing poorly? If not, then reworking your code here doesn't make sense. You need to hit the database either way; to fetch all the data or to see if there is new data. Not to mention the fact that if it isn't performing poorly, you're actually adding an additional database query just to determine if any data has changed. — Justin Helgerson, Nov 19 '12 at 23:06
@Ek0...It is performing poorly. What I provided in my question was a simplified version of what I'm attempting. Assume I'm averaging 10,000,000 points. I don't want to query the entire db each time a request is made for the average if the avg has not changed. However, if 10,000 measurements were added to the data set from a newly completed test then I would have no choice but to query the db to get the updated average. Maybe my question should be if there is a way to check the date that the cached version was created? — lamarant, Nov 20 '12 at 15:05
In that case you could cache for a specific period of time (posted in my answer) or you could hit the database and perform a count of the rows (assuming that will perform reasonably) and then only fetch the data if there is new data (you could store the row count in cache so you have something to compare against). — Justin Helgerson, Nov 20 '12 at 15:24
New data will come in irregularly and quite possibly after long periods of time (days, weeks, maybe never). Is it possible to make the cache never expire and then check the timestamp of the cached data on each request? I could then check that value against the timestamp of the last test in the group and then re-compute only if necessary? — lamarant, Nov 20 '12 at 16:38
Sure, you can cache something for a long period of time. I updated my answer with the code to do it. — Justin Helgerson, Nov 20 '12 at 18:07

score 0 · Answer 1 · answered Nov 19 '12 at 23:10

You will not be able to ascertain whether there are new records without a database trip. If you want to provide caching on your controllers with a time-based expiry, look into OutputCaching. I don't believe you are using any parameters to your action but if you are, you could up the amount of time things are cached and vary by parameter to make sure the results are fetched separately if new parameters are passed in.

Look at output caching write-up here:

http://www.asp.net/mvc/tutorials/older-versions/controllers-and-routing/improving-performance-with-output-caching-cs

Justin Helgerson · Accepted Answer · 2012-11-20T18:06:54.167

If caching for a specific period of time meets your needs you can accomplish this at the view level by doing:

[OutputCache(Duration = 300)] //Cache for 5 minutes.
public ActionResult Average() {
    //Do your stuff!
}

Another option is to just cache the data you need:

HttpRuntime.Cache.Insert(key, value, null, DateTime.MaxValue, System.Web.Caching.Cache.NoSlidingExpiration);

Note in this last example the max cache will probably expire unless you have a very active application or if you configure the application pool process to not have an idle shut down.

.Net MVC Controller force cache refresh depending on situation

2 Answers2