Hi All. Currently my webapp displays all records that are returned as a result of a query on a single page. As the number of records increases this is becoming less than efficient and more than a little unfriendly for users.
This has lead me to investigate mechanisms for paging result sets (or rather turbine 2.1 Vectors ;-). I have looked at how Scarab does this. Scarab basically retrieves all rows, stores a copy in user.temp and returns them to the user a page at a time (where the number of rows per page is selected by the user). While this is going to be efficient from the perspective of the database (particularly when sorting is involved) in that the main query is only executed once, it has the potential to consume an awfully large amount of memory (I'm playing around with a result set that is going to retrieve 7,500 records, enough to cause the JVM running with standard memory settings to throw an OutOfMemoryError). Even if I expanded the amount of memory available to cater for this, what happens when the user wanders off somewhere else in the application leaving that large result set in user.temp - what does Scarab do in this case? An alternative approach will be to use the Criteria setOffset() and setLimit() methods to instruct the database to only return a page worth of rows. The problem with this approach is that I will need to limit the ordering of records to one where a database index exists, otherwise the database will need to retrieve all rows in order to perform its sort before it then throws away all but a page worth of rows. On top of this I am not sure if the setOffset() and setLimit() methods are fully supported in torque (esp. In turbine 2.1) or for that matter the underlying database systems (equivalent SQL appears to exist for MySQL and PostgreSQL, but I haven't checked Oracle or any of the other alternatives). Davis Kulis posted a good tip for the above solution which involves using additional criteria to filter out records that have already been displayed. This would be effective if the result set is sorted by an index value, but perhaps not so useful if all rows need to be retrieved in order for a sort to take place. I am pretty sure it was John McNally that posted details of a prototype class for dealing with pagination (this would have been months and months ago). I was sure I kept a reference to it at the time, but I can no longer find it. I seem to recall that this worked in the same way as described for Scarab above, but If I can be reminded of the class I will gladly review it again. Anyway, this is turning into a very long message. I guess I am interested in some feedback as to how others have handled pagination of very large result sets. As I see it the only answer in this situation may be to go with setOffset()/setLimit() with limited sort options. Got a better solution? Cheers, Scott -- Scott Eade Backstage Technologies Pty. Ltd. Web: http://www.backstagetech.com.au -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
