ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brocard Antoine <antoine.broc...@elca.ch>
Subject RE: How to avoid fetching all rows when accessign a page in a Pag inat edList
Date Fri, 12 May 2006 06:54:40 GMT
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 < antoine.brocard@elca.ch
<mailto:antoine.brocard@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 <http://www.elca.ch> 
Av. de la Harpe 22-24 / Case postale 519 / CH - 1001 Lausanne 
mailto: antoine.brocard@elca.ch <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 <http://www.elca.ch> 
Av. de la Harpe 22-24 / Case postale 519 / CH - 1001 Lausanne
mailto: antoine.brocard@elca.ch  <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.





Mime
View raw message