posted 18 years ago
Hi, this is a common situation and there are a number of solutions.
1) Get rows by numbers. For the first page do your query, take rows 1..10. For the second page, repeat the same query, take rows 11..20. Your database may support syntax that lets you select only "n" rows or even select them by row number. May not get consistent results if other people add and remove rows while you're looking at a page.
2) Get rows by keys. Select the first page. To get the second page select rows with keys higher than row 10. Can't go backwards. May not be consistent.
3) Get all the data and keep it. Copy all rows from the result set into some collection and keep it on the session. You can navigate around the collection by row numbers. May eat up tons of memory for data that is never used. Gets out of date if others add/remove rows.
4) Get all the keys and keep them. Copy all the primary keys from the result set into a collection and keep it. Retrieve the rows you need by the primary keys. Much smaller in memory than #3. Gets out of date.
5) Keep an open cursor on the session. I don't know much about this one, but it sounds expensive.
Any other ideas? Make out a little matrix of the pros and cons for each of these and see if any suit your application. Let us know what you come up with!!
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi