Is there a standard way of making sure consecutive loading of models get the object from cache rather than DB? For example
$product = Mage::getModel('catalog/product')->load(3770);
This will first look at cache storage, if found return that, if not proceed with the loading, saving whats's loaded in to cache and return. This should also be combined with cache remove when ->save() method is called.
I remember there is a module for this, but forgot the name. Anyway, you have to be carefully what you put into such a cache, and how totally you make use of it, as you may end to use cached models, where it harms you.
My personal approach for solving this is a service model I create via Mage::getSingleton() and which I use then to fetch new models (of a specific type) to make use of them in different places.
The advantage of this approach is, if you need a big number of different Models of the same type, you can easy implement a pre-fetching method without changing the way you fetch the single models.
Do you mean inside the same request, or different requests? If you're talking about multiple load calls within the same request, you're supposed to use Mage::registry('current_product') or Mage::registry('product') if you're on a product page to avoid loading the product again. Of course for other controller/actions you can just use the registry to store entities.
If you mean caching between multiple requests, as far as I know there is no standard way to do that. IMO it's also unnecessary. One or two loads should still be very scalable if everything is set-up right (FPC/block-cachin/MySQL tuning, etc.)
If you find yourself loading many products for some reason, consider adding the necessary attributes to the flat table for faster access.
Hope that helped.
query_cache_size and query_cache_type.