Cache First
I'm pretty sure there's an anti-pattern around stored procedures. It seems like the usual problem with distributed applications comes about: people start to think that procedure calls are free, and that there's really no big deal with making a couple of calls to get the data you need, instead of making one call to do it all.
On the other hand, the ORM pattern where loading an object causes a whole bunch of other objects to load from the DB can be incredibly pernicious. There's nothing for taking down a DB like loading up stuff you don't need.
I think the reason the latter pattern is so bad is the execution model for the typical web application: when a client requests a page, the request gets its own thread of execution and that context gets thrown away at the end of the call. This works well with stateless web pages, where you don't necessarily know if the user's coming back. Even on a page where the user might come back and change state on the data just viewed, you have no guarantee that this will happen, or that it will happen in a timely manner. So the model encourages one to simply hit the backing store whenever you need to. A typical interaction model might be:
- GET - SELECT data from the database and render a display with a form to update the data
- POST - user has submitted the form from step 1.
- SELECT the data from the database. You may need to redisplay the form in case of errors in user input, or you may need the original data to validate it or to determine what the user changed.
- UPDATE the record in the database with the new changes
In this model, you can save yourself a SELECT by caching the data, constraining the cache with a time limit to avoid excess memory consumption if the user never updates, or takes a really long time to do so. The problem, of course, is that none of the frameworks out there really support doing this; not seamlessly at least. Everyone has some sort of caching mechanism, via session state or cache, but these are always bolt-on solutions. I'm waiting for the framework that puts the cache first and treats the persistent storage as the afterthought.