> (a) Create a database cursor and page through it > (b) Repeat the query (making sure there is a sort criterium) at each > page request, starting at a variable offset and limiting the > result set > (c) Do the whole query at once, putting the results in some kind > of array. > > same database connection. Has this been done? Or is this a "don't do > that"? It's don't do that > > There is not much to say on variants (b) and (c), I guess. The usual > trade-offs, (b) generating more database activity and (c) being > potentially a memory killer with the usual optimization tricks.
What about a combination? On average, I would guess that most people don't look further than page 3 of your result set. So you could pull out the first (eg) 30 results, and cache those (or at least the ids pointing at those) and only repeat the search if they ask for the subsequent pages. clint