MVC3 data caching techniques

Question

I have a sql query (stored proc) that takes about 8-10seconds to return before the results are displayed in a webgrid. What is best practice for performance regarding cacheing in asp.net-mvc3 so the user doesn't have to take that 8-10sec hit everytime to load that data (less optimizing the query)?

Answer 1

You could use the MemoryCache class to store the result of this query under some key. The key could be the hash of the query criterias (if you have such). And here are some guides on MSDN on how to use it.

When implementing caching bear in mind that this cache is stored in memory by default. This means that if you are running this application in a web farm it might be more interesting to use a distributed cache so that all nodes of the farm share the same cached data. This could be done by extending the ObjectCache class with some distributed caching solution. For example memcached is a popular one and it has .NET provider. Another distributed caching solution is AppFabric.

Answer 2

It's caching this action.

[OutputCache(Duration = 300)]
public ActionResult Action(){

//some operation

return View()
}

Answer 3

How often your underlaying data behind this stored procedure change? If relatively rarely, you can use very good feature - SqlCacheDependency

http://msdn.microsoft.com/en-us/library/ms178604.aspx

This way your heavy SP will be called only when needed, and result will be cached as long as possible.