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

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

I think this would be a nice feature.

Out of curiosity - do people use paginatedList at all because of the way it
works right now? The major drawback is that it loads all the data into
memory. When using paging, I am sure that there would be very few chances
where the result set size would be managable in the memory.

~Rashmi


On 5/12/06, Brocard Antoine <an...@elca.ch> wrote:
>
>  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:rashmid@gmail.com]
> *Sent:* vendredi, 12. mai 2006 08:40
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: How to avoid fetching all rows when accessign a page in a
> Paginat edList
>
> 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 &gt; #rowNoGreaterThan# AND
>
> ROWNO &lt; #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.
> >
> >
>