Hi 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.
