Here is one small wisdom that you must never forget:

• Memory is virtual.
• Virtual memory is a disk file, and must therefore be treated accordingly.

Virtual memory is, of course, a very cleverly buffered “disk file,” such that in small amounts and with good locality-of-reference patterns you can ignore the difference. The buffer-space available is also plentious. But if your app becomes a “thousand-pound elephant” within the virtual-memory space... “you're scroo-o-o-o'd.”

When designing any database application, you should assume: (a) that the tables are arbitrarily large, and (b) that the database-engine knows what it is doing with regards to properly handling them. Use carefully-designed queries that will enable the database engine to be as specific as possible in what it returns to you. Don't try to buffer those rows further yourself, i.e. “in the name of efficiency,” because it won't be.

If you find that your application is running too slow, take a very close look at the queries that it is issuing ... in particular, how many. Lots of apps issue millions of queries during their lifetime.

If there is one, single “parting shot” I would make, it is this ... remember sorting. When you know that a result-set is sorted in a certain way, a change in value(s) between one record and the next (only two records need be compared...) is significant. Sorting is an uncommonly-fast operation, and unexpectedly efficient. And you get it all with just two words:   ORDER BY.