(shamelessly cross posted from the Community Exchange)
I am writing a module that displays a list of items on the page. The database holds (lets say) 50,000 items which are related (many to many) with (lets say) 1000 pages.
Instead of writing a stored procedure to return a set of complete items for a given page from the database (i.e. all columns, in order to hydrate a list of item objects), I am thinking of doing the following to render a list:
- on application startup, caching all listing items
- getting the SP to return just the key value for the items
- looping through the list of keys & retrieving the matching object from cache
Preliminary tests indicate this performs faster than hydrating a list from the DB for each page request. It also seems to cause less overall memory consumption, as each page request would just look up existing objects, as opposed to each request making a private list (when traffic is high and large pages are requested memory consumption gets very high).
So now I am debating with fellow developers how to implement the cache. We have come up with several options, and this is where I would appreciate your opinions:
- Use the native ASP.NET HttpContext.Current.Cache
- Use the DNN DataCache wrapper
- Use static/singleton objects
Within each of these there is also a choice whether to:
a) Cache each item object individually
b) Build a dictionary (or similar list) containing all item objects then cache the dictionary
Read performance is the main concern. Maintenance is a secondary concern - we would write our GetItems methods defensively to check if each item is in the cache and if not, retrieve it from the DB and insert it into the cache.
I know there is no right answer ... there are pros & cons for everything. I am just looking to see if people have pitfalls they can share about any of the above, or if any approach stands out as being by far the best performing, or is particularly hard to merge new data into.
Thanks