This is also a problem I'm facing and I too am looking for an efficient maaner to do pagination without hitting the DB for every page, subsequently it would be handy if after clicking on one of the entries on the index page one sees all the info pertaining to that entry. Again without having to rehit the DB.
My concern is the validity of data and how long it should be kept in memory, whether that being a GlobalCache or user.temp. I haven't looked at the Scarab code, but when does the user.Temp info get cleared? If at all. Would it not be wise to use a combination of both solutions? That you limit the result to say 500 rows (using Criteria setOffset() and setLimit() methods) which you could put into user.temp for pagination? /Colin > 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]> > > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
