Business layer design dilemma: memory or IO?

Question

The project I am working on is facing a design dilemma on how to get objects and collections of objects from a database. Sometimes it is useful to buffer *all* objects from the database with its properties into memory, sometimes it is useful to just set an object id and query its properties on-demand (1 db call per object to get all properties). And in many cases, collections need to support both buffering objects into memory and being initialized with minimum information for on-demand access. After all, not everything can be buffered into memory and not everything can be read on-demand. It is a ubiquitous memory vs IO problem.

Did anyone have to face the same problem? How did affect your design? What are the tough lessons learned? Any other thoughts and recommendations?

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

However, if this same API is going to be consumed by a web service to display all products with details, the network traffic will become chatty. The better sequence in this case would be:
1. What the heck, buffer all products and product fields from just one db call (in this case buffering 1 million products also looks scary)

Answer 1

It depends how often the data changes. It is common to cache static and near static data (usually with a cache expiry window).

Databases are already designed to cache data, so provided network I/O is not a bottleneck, let the database do what it is good at.

Have you looked at some of the caching technologies available?

• .NET Framework 4 ObjectCache Class

• Cache Class: Using the ASP.NET Cache outside of ASP.NET

• Velocity: Build Better Data-Driven Apps With Distributed Caching

• Object cache for C#