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. :)
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.