HttpContext.Current.Cache VS. SQL Table Performance

asp.net-mvc-4 caching dapper performance sql-server

Question

We have a poorly designed shopping cart database. All processed objects that will be used to the front site are stored in HttpContext.Current.Cache on Application_Start. Processed objects I mean results from sql script that has many joins and where conditions.

Looking for best solution to remove caching or improve the current caching process. I'm thinking of storing the processed objects to a SQL Server table that will be repopulated every midnight. And use Dapper ORM to retrieve data from this SQL Server table and implement output caching.

Hope someone will share a high speed and maintainable solution for this problem. :)

Thanks!

Expert Answer

What you are describing is really : duplicating the data into a second (technically redundant) model, more suitable for query. If that is the case, then sure : have fun with that - that isn't exactly uncommon. However, before doing all that, you might want to try indexed views - it could be that this solves most everything without you having to write all the maintenance code.

I would suggest, however, not to "remove caching" - but simply "make the cache expire at some point"; there's an important difference. Hitting the database for the same data on every single request is not a great idea.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow