I've found you need to do two things: push to the database what is easier to do in the database (filtering and sorting, usually), and don't let the DB or ORM layers buffer if you don't need it.

My expertise here is web servers, specifically PHP on MySQL. MySQL does filtering, sorting and paging faster and more efficiently than in the PHP, so I expose these controls through the object interface. They get converted into the right SQL clauses as required. I also use an unbuffered query whenever I can get away with it and make it easy for the app to pull one row at a time (a generator, basically). I've seen this reduce memory usage by orders of magnitude and speed to increase substantially.

I think this approach is kind of halfway between your two extremes.

Wade.