Increase efficiency of paging using a list subset (patch included)

====
imported from sf tracker
id 872183
submitted by Ian Barnett - ianbdev
http://sourceforge.net/support/tracker.php?aid=872183
====

We encountered a situation where we had to support a
query that resulted in 1000's or rows (I wouldn't ask why
it's a very long story).

Loading all this from the DB into the application server
memory was terribly wasteful and resource hungry.

We came up with a db list paging solution (borrowing a
suggestion from askTom and incorporating in our app)
that allowed us to retrieve just one page of records from
a given page number (with a known page size).

The problem was than to get DisplayTag to take the
abbreviated list of just 100 records and display them and
continue to manage the page navigation correctly (i.e.
to show that we are at page 5 of approx 66 pages
where there are 6650 records total).

We managed to do this with some changes to the
DisplayTag code (see attached).

The application is responsible for getting the abbreviated
list of records and the total list size and passing it to the
table tag.

If this can be implemented in the display tag project we
would be most appreciative. Hopefully others will find the
feature useful as well.

=====





Date: 2004-09-13 21:47
Sender: ralfhauser
Logged In: YES
user_id=266141

see for another solution
http://sourceforge.net/tracker/index.php?func=detail&aid=1026408&
group_id=73068&atid=536613

Date: 2004-08-30 21:06
Sender: nobody
Logged In: NO

Thanks for the changes, this is great. One thing though, the
summary doesn't display the correct record numbers when you
are on a page other than page 1.

here is a fix for that problem.

If you use the changes specified for getFirstIndexForPage
and getLastIndexForPage in new methods called
getFirstVirtualIndexForPage and getLastVirtualIndexForPage
and return the getFirstIndex and getLastIndex to their
original methods. then change the calls in getListForPage to
use these new getFirstVirtualIndexForPage and
getLastVirtualIndexForPage everything will work as expected.

Again thanks for this change it was great to not have to
write the entire thing

Date: 2004-08-21 22:27
Sender: wglass
Logged In: YES
user_id=643158

Hi,

I've created an alternate implementation of these feature.
No offense meant to the original poster, I hadn't seen this
issue when I created it. I couldn't figure out how to add it
as
an additional attachment to this issue, so I created a new
issue # 1013526 .

In a nutshell, the new implementation adds two new
properties:

paging.manual (true/false, default: false)
paging.manual.prefix (default "d")

If paging.manual is set to true, the Java servlet that
generates the list provides the sublist along with a attribute
giving the list size. See the other issue report for the
details.

This implementation was developed against DisplayTag 1.0rc2
and is compatible with the Expression Language (EL) tagset.
Hope it's useful.

Date: 2004-05-31 00:19
Sender: carlossg
Logged In: YES
user_id=792979

I've developed a solution without changing displaytag sources.

Basically it uses a PaginatedList where only acceses to the
current page are allowed. The interator over a PaginatedList
returns null for other than current page, this doesn't interfere
with displaytag.

You can check it at http://oness.sourceforge.net

http://oness.sourceforge.net/multiproject/oness-common-
model/xref/net/sf/one
ss/common/model/util/package-summary.html

http://oness.sourceforge.net/multiproject/oness-party-
webapp/xref/net/sf/one
ss/party/webapp/controller/action/package-summary.html

http://oness.sourceforge.net/multiproject/oness-common-
webapp/xref/net/sf/on
ess/common/webapp/controller/action/ActionUtils.html

http://cvs.sourceforge.net/viewcvs.py/oness/party/webapp/s
rc/webapp/party/li
stParty.jsp

It doesn't support sorting (yet).


Carlos Sanchez
A Coruña, Spain

Oness Project
http://oness.sourceforge.net

Date: 2004-05-17 12:47
Sender: carlossg
Logged In: YES
user_id=792979

+1 VOTE
I think this is very needed as normally the user wants a small
number of results and has no sense to load all rows from
database.

About implementation I have a model facade search method
with firstElement and maxElements parameters, that
delegates to a DAO.
Using Hibernate in the DAO you can call setFirstResult and
setMaxResults in a Criteria object.

Date: 2004-04-28 08:22
Sender: ndbabu
Logged In: YES
user_id=1025755

I experimented with the modified tlds and class files based
off on the release 1.0b3. It is working with some more
modifications. I don't know how can attach the modified
files. Thanks to display tag developers crew.

Date: 2004-01-12 22:30
Sender: ianbdev
Logged In: YES
user_id=461701

As an alternate implementation note - it was raised by a
member of our team that the list size might always be passed
in to avoid conditional coding in the JSP and have the tag
determine whether the actual list size matched the virtual list
size parameter as passed in. If there was no match it may be
reasonable to assume the actual list is just a subset list - if
they sizes match one could assume the list is complete.

Date: 2004-01-12 22:29
Sender: ianbdev
Logged In: YES
user_id=461701

As an alternate implementation note - it was raised by a
member of our team that the list size might always be passed
in to avoid conditional coding in the JSP and have the tag
determine whether the actual list size matched the virtual list
size parameter as passed in. If there was no match it may be
reasonable to assume the actual list is just a subset list - if
they sizes match one could assume the list is complete.

Date: 2004-01-12 22:27
Sender: ianbdev
Logged In: YES
user_id=461701

As an alternate implementation note - it was raised by a
member of our team that the list size might always be passed
in to avoid conditional coding in the JSP and have the tag
determine whether the actual list size matched the virtual list
size parameter as passed in. If there was no match it may be
reasonable to assume the actual list is just a subset list - if
they sizes match one could assume the list is complete.

Date: 2004-01-07 23:03
Sender: ianbdev
Logged In: YES
user_id=461701

OK - I get it - the JSP code tags are being interpreted here
(doh) - anyway just put the value for the full list size in this
parameter.

Date: 2004-01-07 22:58
Sender: nobody
Logged In: NO

oops - the jsp code didn't show up... trying again (just the
new parameter part)...

virtualSize="<%= ((Integer)request.getAttribute
(CodeListAction.CODE_LIST_SIZE_KEY)).toString() %>"

Date: 2004-01-07 22:54
Sender: nobody
Logged In: NO

Sorry - forgot to mention the code is based off the 1.0.b2
codebase if you want to do the diff.

I should also point out that the code is still hot off the
development gridle. It has been tested to ensure the paging
navigation works with the abbreviated list and it should work
normally (i.e. with full lists) if the virtualSize parameter
(i'm
certainly not married to this name - suggestion box is open...)
is not set or set to -1.

Since my knowledge of the entire DisplayTag codebase is
limited I cannot say for certain that there are no other ill
side
effects of this change.

I can say that certain things will definitely not work with the
current assumptions in the DisplayTag codebase (i.e. that the
code always has access to the full list).

For instance you cannot sort the full list in DisplayTag if you
only have part of it (the effect would be to sort the page
only). A more comprehensive approach might say "if the
application is trying to sort the full list then don't even
attempt it - just action the sort url with the sort column
parameter and the let the application handle the full list
sorting...). I have not coded for that case.


Of interest might be the code snippets that do the server side
work (we are using JSPs, Struts Actions, and Oracle DB).

JSP:

    <display:table class="list" name="<%=
CodeListAction.CODE_LIST_KEY %>" id="row" pageSize="20"
virtualSize="<%= ((Integer)request.getAttribute
(CodeListAction.CODE_LIST_SIZE_KEY)).toString() %>"
requestURI="codelist.do" sort="list">

(the request attribute should probably just be stored as a
string to stop the excess coding here)

ACTION:

        // Get the page number requested
        int page = 1;
        int size = 20;
        Enumeration paramNames = request.getParameterNames
();
        while (paramNames.hasMoreElements()) {
            String name = (String)paramNames.nextElement();
            if (name != null && name.startsWith("d-") &&
name.endsWith("-p")) {
                String pageValue = request.getParameter(name);
                if (pageValue != null) {
                    page = Integer.parseInt(pageValue);
                }
            }
        }

(suggestions welcome for a better way to decipher the
DisplayTag parameter prefix...Also the page size may vary in
the JSP so this value should be placed in the request -
DisplayTag could add the pagesize into the request url if the
virtualSize parameter is being used)

DB:

There are a number of ways to handle this bit - stored
procedures, two db calls, cached data, etc. For now we are
just making two calls - one to get the list size and one to get
the page of data we need - we could probably check the
where clause of the query for any filtering changes before
getting the size again if we know the user is still paging
through the same list range - but that'll come later...;-)

This bit applies to Oracle - you'll need to come up with your
own version for other DB's (I got this from askTom):

The paging sql code should wrap your own query as a
complete inner query):

select * from (
  select query.*, rownum rnum from (

    your complete query goes here....

  ) query where rownum <= (:pagingNo*:pagingSize)
) where rnum >= ((:pagingNo-1)*:pagingSize)+1 order by rnum

askTom also suggests using the FIRST_ROWS hint if you have
an excessively large number of rows (search for
subject "getting rows N through M of a result set")


BTW: I forgot to mention before but big thanks to the
DisplayTag development crew - we just love this library!

Date: 2004-01-07 15:50
Sender: javajedi
Logged In: YES
user_id=62441

Awesome. I was going to do this, but didn't in the hopes
that someone else would first. :) It seemed like a very
obvious needed fix to displaytag, but looked like it
wouldn't be trivial to implement. I didn't look through the
patch very closely since it wasn't submitted as a diff, but
if it works, I would second the hope that it can be pulled
back into the main source base.