scrolling in large resultsets with SQL
This article on the SQLite wiki details how to efficiently maintain a small window on a big dataset without sacrificing performance, in environments that cannot maintain actual cursors, or where doing so is prohibitively expensive. Rule 2 in the article applies to other databases too; the method detailed in the article applies to any database in which keeping cursors is either impossible or expensive.
The article seems obvious; but I had not actually thought of that approach myself, so far.