Business-level design dilemma: memory or IO?

Question

The project I'm working on faces a constructive dilemma on how to get objects and collections of objects from a database. Sometimes it is useful to buffer objects * all * from the database with its properties into memory, sometimes it is useful to simply set the object identifier and request its properties upon request (1 bit of a call to the object to get all properties). And in many cases, collections must support both buffer objects in memory and be initialized with minimal information for on-demand access. After all, not everything can be buffered in memory, and not everything can be read on request. This is a widespread problem of memory and IO.

Has anyone encountered the same problem? How did you influence your design? What are the hard lessons? Any other thoughts and recommendations?

EDIT: My project is a classic example of a business-level DLL consumed by a web application, web services, and desktop application. When a list of products is requested for the desktop application and is displayed only by the name of the product, it is normal for this sequence of steps to display all products (say, there are a million products in the database):
1. One db call to get all product names
2. One db call to get all the product information if the user clicks on the product to view information (access on demand)

However, if the same API is consumed by the web service to display all products with detailed information, network traffic will become frequent. The best sequence in this case would be:
1. What the hell, buffer all products and product fields with just one db call (in this case, buffering 1 million products also looks scary)

Answer 1

It depends on how often the data changes. Static and near static data are usually cached (usually with a cache expiration window).

Databases are already designed for data caching, so the provided network I / O operations are not a bottleneck, allowing the database to do what is good.

Have you looked at some of the caching technologies?

• .NET Framework 4 ObjectCache Class

• Cache Class : Using ASP.NET Cache Outside of ASP.NET

• Speed: Creating Enhanced Distributed Caching Applications

• Object Cache for C #

Answer 2

This is not a popular position, but avoid all caching if it is absolutely necessary, or if you know for sure that you will need an "Internet scale." Tried to expand the layered cache on top of the database? Are you going to write through the cache and only read or wait for the LRU object to write the changes? What happens when another application or web services layer sits on a database and receives incompatible readings?

Most modern databases already have a cache and can probably implement them better than you, just determine if you want to hit the DB cable every time you need something. In the vast majority of cases, the database will work fine, and you will save your sequence. The theory of BASE and CAP is good and interesting to talk and imagine, but sometimes you just can’t win in the market just by getting into a good old database. Stress test and identify your hot spots, if necessary, save your cache.