How to avoid fetching all rows when accessign a page in a Paginat edList

[Brocard Antoine <an...@elca.ch>]

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:antoine.brocard@elca.ch / 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:antoine.brocard@elca.ch / 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.

Re: How to avoid fetching all rows when accessign a page in a Paginat edList

[Rashmi Dixit <ra...@gmail.com>]

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 <an...@elca.ch> 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:antoine.brocard@elca.ch / 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:antoine.brocard@elca.ch / 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.
>
>