Thanks!
This
is the direction I was going to take...
Now my
bonus question is:
Is
there a way (maybe with new xml element in sqlmap) to integrate this a little
bit more in IBatis?
What
about having something like:
<selectCount id="selectCountXXX">
select count(XXX) from YYY where
CONDITION
</selectCount>
<selectWithRange id="selectXXX" range="and where rownum >#X# and
rownum <#Y#" fromParam="X" toParam="Y">
select x,y,z from YYY where CONDITION
</selectionWithRange>
an
then in the code:
sqlMapClient.executeQueryForPaginatedList("selectCountXXX", "selectXXX",
param).
The
returned paginatedList will fetche the DB again when we call
gotoPage(...) or prev(), next(), etc...
The
call to the given statements and the X,Y parameters handling would then be
managed by iBatis itself instead than by some hand-made, custom mechanism on top
of it (that I'm actually writting). This would really increase the power of
PaginatedList. But maybe this would go against the KISS principle behind iBatis,
I don't know...
What
do you folks think about that?
Antoine
-----Original Message-----
From: Rashmi Dixit [mailto:[EMAIL PROTECTED]
Sent: vendredi, 12. mai 2006 08:40
To: [email protected]
Subject: Re: How to avoid fetching all rows when accessign a page in a Paginat edListHi Antoine,
We solved a similar problem in our product.We used Oracle queries to retrieve only those results that we were going to display in the requested for page. For this we used ROW_NUM in Oracle.
- we query the DB to find out the total count of rows that the select is going to return
- Using the count, we find out the number of pages that we need to display.
- Keep an index such that we have the minimum row number and maximum row number to display.
- fire a query which selects the ROW_NUMBER() in the result set. The Where clause in this query is built as
ROWNO > #rowNoGreaterThan# AND
ROWNO < #rowNoLessThan#
This has made our application very fast and also leaves a smaller footprint.
Hope this helps!
Regards
~Rashmi
On 5/12/06, Brocard Antoine <[EMAIL PROTECTED]> wrote:Hi all,
I have the following problem:
I have a table in the database that contains about 26'000 rows. I would like
to display it using pagination. So I decided to use
SqlMapExecutor.executeQueryForPaginatedList (...).
The paging works fine, but when I try to access pages (with the gotoPage(n)
method) that are pretty far from 0 (~1000), it takes a lot of time. I
suspect iBatis to fetch all the row from 0 to my page.
Is there a way to improve this? I use Oracle that has the ROW_NUM property
associated to each row. Is there a way to modify my sql statement so that it
receives the "asked" page somehow and can maybe return only the interesting
rows?
Thank you very much for your answer
Best regards
Antoine
Antoine Brocard / Consultant
ELCA / www.elca.ch
Av. de la Harpe 22-24 / Case postale 519 / CH - 1001 Lausanne
mailto:[EMAIL PROTECTED] / Tel: +41 21 613 21 11 / Fax: +41 21 613 21
00
This message may contain confidential and/or privileged information. If you
are neither the addressee nor authorized to receive this for the addressee,
you must not use, copy, disclose or take any action based on this message or
any information herein. If you have received this message in error, please
contact the sender and delete this message. Thank you.
Antoine Brocard / Consultant
ELCA / www.elca.ch
Av. de la Harpe 22-24 / Case postale 519 / CH - 1001 Lausanne
mailto:[EMAIL PROTECTED] / Tel: +41 21 613 21 11 / Fax: +41 21 613 21
00
This message may contain confidential and/or privileged information. If you
are neither the addressee nor authorized to receive this for the addressee,
you must not use, copy, disclose or take any action based on this message or
any information herein. If you have received this message in error, please
contact the sender and delete this message. Thank you.
