6

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)?

genxgeek
  • 13,109
  • 38
  • 135
  • 217

3 Answers3

11

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.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • MemoryCache is probably the right solution, but I've got some scenarios in which Objects are automatically evicted from cache (with a MVC 3). What is your advice regarding that ? Are there any particular configurations need for make it work with MVC ? Oh well if you want to give it a shot, can you please read http://stackoverflow.com/questions/7849000/memorycache-empty-just-after-adding-an-object ? I'm still fighting with that issue and cannot understand where I'm failing. :( Thanks. – BigMike Dec 13 '11 at 08:17
  • @BigMike, you can specify an expiration policy when storing objects into the cache. You could define absolute expiration, sliding expiration, change monitors, ... It is very flexible. – Darin Dimitrov Dec 13 '11 at 08:20
  • yes I can, I've tryed with both sliding expiration and absolute time expiration. In some scenarios I've got everything evicted for apparently no reason. Funny thing is that in my remove callback I've get a Reason of CacheSpecificEviction but cannot find who's actually evicting. – BigMike Dec 13 '11 at 08:28
3

It's caching this action.

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

//some operation

return View()
}
Oleksandr Fentsyk
  • 5,256
  • 5
  • 34
  • 41
  • That will cache the entire action (e.g. the action won't be even executed), so even if data changes it won't be executed for the next 5 min. Sometimes it can help, but when accessing data I prefer not to use it. – BigMike Dec 13 '11 at 08:15
2

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.

rouen
  • 5,003
  • 2
  • 25
  • 48
  • Thx for the reply. This will probably need to be updated about every 3-4 hours. However, I did get SqlCachDependecy to work so thanks but I will probably need to update it more frequent. – genxgeek Dec 13 '11 at 23:39