Thank you very much Scott. I too will be looking into paging controls and this helps a bunch!
-warner ----- Original Message ----- From: "Scott Eade" <[EMAIL PROTECTED]> To: "turbine-user" <[EMAIL PROTECTED]> Sent: Monday, July 15, 2002 8:22 PM Subject: Re: Pagination strategies - a reasonably complete review (long) > Yes, some kind of combination may be the best way to go - read on... > > A little more research has revealed: > > 1. That turbine 2.1 torque provides support for database server side: > > limit: > MySQL > PostgreSQL > Sybase > offset: > MySQL > PostgreSQL > > However this is expanded in torque 3.0 which supports: > > limit: > MySQL > MSSQL > Oracle > PostgreSQL > Sybase > offset: > MySQL > PostgreSQL > > Strangely the relevant code in BasePeer doesn't seem to use the limits for > MSSQL, Sybase - perhaps this is not yet fully implemented. > > Offset can be supported in Oracle using the same mechanism as for limit - > i.e. The pseudo column "rownum" (use "where rownum < offset + limit" or > "where rownum between 1 and 11"). It looks to me like it would be > relatively straightforward to implement the offset functionality for oracle > (if anyone is interested in doing this the relevant classes are DBOracle and > BasePeer). > > 2. BasePeer provides services for limiting the number of rows that are > returned. If the server does not implement the offset and limit, the values > are passed through to Village which I assume discards the unwanted data. > This is excellent as it means that setLimit() and setOffset() should work on > all database platforms - the trick is that it will be more efficient when > the support is provided by the database server. Note that a non-index sort > requirement will still mess things up with this approach. > > 3. John McNally's class that I mentioned below is: > > org.apache.turbine.util.db.LargeSelect > > Which has been moved to the attic in jakarta-turbine-2 recently in > preparation for turbine 2.2. The good news is that this class is being > carried forward in jakarta-turbine-torque as: > > org.apache.torque.util.LargeSelect > > According to the comments this is an as yet untested class. It does however > appear to be an excellent implementation of the combination of the other two > solutions including a background thread that retrieves the data up to a > specified limit of rows. In my case, with 7500 rows I could retrieve say > the middle 500 rows and page through them 50 at a time. If I page off the > end of my 500 rows a new query will be executed to retrieve the next 500 > rows, if I am go backwards past the beginning of the 500 rows the class > jumps back 2 times my page size (100) in anticipation that I may go back > another page and re-executes the query. The query itself uses BasePeer, so > the comments mentioned above under items 1 and 2 apply. If you are using a > non-index based sort criteria then all rows will be retrieved by the DB in > order to perform the sort each time the query is executed, however this is > likely to be as good a trade off as any. > > While the comments indicate that some debugging may be required, this looks > like an excellent starting point - anyone interested in paging result sets > should be working on this. > > > With regards to your comment concerning the ability to reuse the data > available to the list query for the detail page, I would imagine that > LargeSelect could be enhanced to support this. It would be worth figuring > out a way to do this that would also signal to the LargeSelect that it has > completed its processing and can thus release its resources - otherwise you > have a largish chunk of memory being consumed by the LargeSelect that the > user is no longer interested in. Conversely, it is good SQL practice to > select only the columns you are interested in for your list page which might > impact your ability to reuse this data on a detail page. > > > Not sure when I am going to get to dive in and use this - hopefully within > the next month or so. Mean time I hope the list benefits from my > investigations. > > Cheers, > > Scott > -- > Scott Eade > Backstage Technologies Pty. Ltd. > Web: http://www.backstagetech.com.au > > > > From: "Colin Chalmers" <[EMAIL PROTECTED]> > > Reply-To: "Turbine Users List" <[EMAIL PROTECTED]> > > Date: Mon, 15 Jul 2002 17:11:33 +0200 > > To: "Turbine Users List" <[EMAIL PROTECTED]> > > Subject: Re: Pagination strategies > > > > 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]> > > > > > -- > 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]>
