You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Scott Eade <se...@backstagetech.com.au> on 2002/07/15 16:33:48 UTC
Pagination strategies
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: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Pagination strategies - a reasonably complete review (long)
Posted by Warner Onstine <sw...@warneronstine.com>.
Thank you very much Scott. I too will be looking into paging controls and
this helps a bunch!
-warner
----- Original Message -----
From: "Scott Eade" <se...@backstagetech.com.au>
To: "turbine-user" <tu...@jakarta.apache.org>
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" <co...@maxware.nl>
> > Reply-To: "Turbine Users List" <tu...@jakarta.apache.org>
> > Date: Mon, 15 Jul 2002 17:11:33 +0200
> > To: "Turbine Users List" <tu...@jakarta.apache.org>
> > 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:
> > <ma...@jakarta.apache.org>
> >> For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >>
> >>
> >
> >
> > --
> > To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> > For additional commands, e-mail:
<ma...@jakarta.apache.org>
> >
>
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Pagination strategies - LargeSelect, a work in progress
Posted by Scott Eade <se...@backstagetech.com.au>.
I am looking at org.apache.turbine.util.db.LargeSelect (t2.1, in torque it
is org.apache.torque.util.LargeSelect) with a view to using it for my
paging needs. As this was discussed very recently (refer to the earlier
Pagination strategies thread) I thought a few of you may be interested in
how this is coming along.
As I am apparently the first user of LargeSelect I am having lots of fun
(not!) debugging it and extending it to suit my needs.
Here are a few of the problems I have run into so far along my solutions and
dead ends:
1. In LargeSelect.run() it is necessary to initialise the results Vector (do
the conversion to ArrayList in your head for torque).
2. In LargeSelect.run() it is necessary to invoke qds.clearRecords() to
recycle the QueryDataSet at the end of the while loop that retrieves the
records.
3. Even though LargeSelect accepts a name (and provides a default) to use as
the key to use to store itself in the session it does not do the storing. I
have just been sticking it in user.temp as this seems as good a place as
any.
4. I am sort of used to the Peer classes populating the select columns
automatically, this does not occur with LargeSelect so you may want to
invoke your peer class addSelectColumns(criteria) method before
instantiating a LargeSelect. Of course it is better in these situations to
only retrieve the minimum of columns, a way to do this is mentioned shortly.
5. LargeSelect returns village Record objects, while manageable, there is a
better way of dealing with this. I have created a new interface,
org.apache.turbine.util.db.Populatable (again, do the conversion to
org.apache.torque.util in your head for torque) that defines a
populateObject(Record, int) method and added a constructor to LargeSelect
that includes a String argument that defines a class name that implements
Populatable. I can then create a class, e.g. UserListItem, that implements
Populatable which provides the mapping from a village Record to an instance
of the class (I will be working towards bending this to allow the regular
Peer classes to be used here, but these will require some modification - the
populateObject() method provided by the peers is a Static and I am unsure
how I can tell if this is available on an arbitrary class).
6. LargeSelect cannot currently provide any information as to how many
results in total will be returned. I am working on an extension class,
LargeNumeratedSelect that provides this information however I am running
into the following problems:
a) The Criteria class doesn't allow addSelectColumn("COUNT(*)") without
modification.
b) MySQL won't allow "COUNT(*)" in a query without a GROUP BY clause
(this seems crazy to me - all I want to know is how many rows are returned
by the query, I am pretty sure that DB2 and Oracle allow this).
c) The Turbine 2.1 Criteria class does not support GROUP BY so I can
implement a workaround that adds a dummy constant column to group by.
This means that I effectively have to abandon support for
LargeNumeratedSelect until I move to the decoupled torque (been holding back
so far because of my need to extend TurbineUser).
7. Other issues exist - I am only currently scratching the surface.
I'll keep plodding along. Once I get somewhere I will port my changes
forward to the decoupled torque and post a patch. This won't have been
tested using torque, but neither has the current class anyway. For that
matter I may as well include my untested LargeNumeratedSelect class. If
somebody working with the decoupled torque wants to work with me on this we
may get somewhere much faster.
Cheers,
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
Web: http://www.backstagetech.com.au
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Pagination strategies - a reasonably complete review (long)
Posted by Scott Eade <se...@backstagetech.com.au>.
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" <co...@maxware.nl>
> Reply-To: "Turbine Users List" <tu...@jakarta.apache.org>
> Date: Mon, 15 Jul 2002 17:11:33 +0200
> To: "Turbine Users List" <tu...@jakarta.apache.org>
> 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:
> <ma...@jakarta.apache.org>
>> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>>
>>
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Pagination strategies
Posted by Colin Chalmers <co...@maxware.nl>.
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:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>