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