And I ran some hprof tests just to move beyond my disbelief. The client cache looks to get filled all at one shot. At least in a 1K vs 10K test doing a size() like activity (resultSet.last(), resultSet.getRow()), heap usage went up 10 times. Ouch.
> -----Original Message----- > From: Matthew Baird [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 25, 2003 3:33 PM > To: OJB Users List > Subject: RE: Using setStartAtIndex with Oracle 8i > > > ok, so it's just a small (but probably important) > optimization. thanks for the heads up. > > -----Original Message----- > From: Ebersole, Steven [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 25, 2003 11:52 AM > To: 'OJB Users List' > Subject: RE: Using setStartAtIndex with Oracle 8i > > > The problem is that you can only bound the upper end of the > results, there > is no way (that I know of) to specify a "start row". I think > the best your > going to be able to do is to set the Statement.maxRows = > endAtIndex, which > will append a ROWNUM constraint to the query. But as you get > towards the > end of the result set, this will become pointless. > > In short, this is simply not possible in Oracle (8i or > earlier). Becuase > the server cursors cannot be scrolled you would always have to scroll > through the first n-1 rows in order to get to row n as the > startAtIndex. > > > |-----Original Message----- > |From: Matthew Baird [mailto:[EMAIL PROTECTED] > |Sent: Tuesday, March 25, 2003 1:27 PM > |To: OJB Users List > |Subject: RE: Using setStartAtIndex with Oracle 8i > | > | > |sounds like there is a chance to do a platform specific > |optimization that starts at and rturns a limited number of > |rows for Oracle. Anyone know the specifics of how to do > |this? I'm thinking something along the lines of the > |.size() vs COUNT(*) method of finding the number of > |returned results. > | > |-----Original Message----- > |From: Ebersole, Steven [mailto:[EMAIL PROTECTED] > |Sent: Tuesday, March 25, 2003 11:13 AM > |To: 'OJB Users List' > |Subject: RE: Using setStartAtIndex with Oracle 8i > | > | > |Not sure how it actually gets filled (all at once vs > |asynchronously). > | > |FYI, 9i does support scrollable cursors and I believe that > |is then extended > |into the JDBC drivers. Might want to check out upgrading > |if this is a big > |concern. > | > | > | > | |-----Original Message----- > | |From: Andrew Gilbert [mailto:[EMAIL PROTECTED] > | |Sent: Tuesday, March 25, 2003 12:47 PM > | |To: OJB Users List > | |Subject: RE: Using setStartAtIndex with Oracle 8i > | | > | | > | |Learn something new every day... > | | > | |http://otn.oracle.co.kr/docs/Oracle817/java.817/a83724/resl > | |tse1.htm#1017614 > | | > | |Steven is correct, in that the db doesn't implement > | |scrollable cursors, so the Oracle solution is dependent on > | |client side caching. > | | > | |It isn't clear to me how fast the cache gets filled, ie > | |all at once or in response to movement. In our case we are > | |paging and seldom ever iterate more than a few percentage > | |points of a large set. Perhaps that explains our ignorant > | |happiness? > | | > | |So we obviously need to look at > OracleResultSetCache as well. > | | > | |Thanks Steven. > | | > | |> -----Original Message----- > | |> From: Ebersole, Steven > [mailto:[EMAIL PROTECTED] > | |> Sent: Tuesday, March 25, 2003 12:57 PM > | |> To: 'OJB Users List' > | |> Subject: RE: Using setStartAtIndex with Oracle 8i > | |> > | |> > | |> Oracle 8i (and previous) does _not_ support scrollable > | |> cursors. Thus, the > | |> entire result set is transferred into a > client-side memory > | |> cache for result > | |> sets declared as scrollable. All result set methods are > | |then executed > | |> against this memory cache. Obviously, for very > |large cursors > | |> this will be > | |> very inefficient. > | |> > | |> Your best options are: > | |> 1) Don't use scrollable result sets; > | |> 2) Implement a better client-side cursor cache ( see the > | |> javadocs for the > | |> OracleResultSetCache interface for more details) > | |> > | |> > | |> > | |> > | |> |-----Original Message----- > | |> |From: Matthew Baird [mailto:[EMAIL PROTECTED] > | |> |Sent: Tuesday, March 25, 2003 11:40 AM > | |> |To: OJB Users List > | |> |Subject: RE: Using setStartAtIndex with Oracle 8i > | |> | > | |> | > | |> |are there any oracle experts that could shed some > | |light on this. > | |> | > | |> |the actual code should not bring back all the > |results and > | |> |only return a subset, but rather it should use the > | |> |ResultSet.absolute method to bop around and > |only retrieve > | |> |the objects it needs. This code is in RsIterator and > | |> |PersistenceBrokerImpl > | |> | > | |> |anyone? > | |> | > | |> |-----Original Message----- > | |> |From: [EMAIL PROTECTED] > | |> |[mailto:[EMAIL PROTECTED] > | |> |Sent: Tuesday, March 25, 2003 9:29 AM > | |> |To: OJB Users List > | |> |Subject: RE: Using setStartAtIndex with Oracle 8i > | |> | > | |> | > | |> | > | |> |Hi, > | |> | > | |> |I think when you use setStartIndex method, all > |the results > | |> |are retrieved > | |> |from DB and then your query returns only the > |subset that > | |> |you specified. > | |> |I ran into that problem too and I had to add > |an additional > | |> |criteria to my > | |> |query : > | |> | > | |> |Criteria crit = new Criteria(); > | |> |crit.addSql("rownum < 100"); > | |> |Query q = QueryFactory.newQuery(Foo.class, crit); > | |> |Collection c = broker.getCollectionByQuery(q); > | |> | > | |> |The problem is that it uses oracle specific stuff. > | |> |But at least, performance is much better ! > | |> |Hope that helps, > | |> | > | |> |Thomas > | |> | > | |> | > | |> | > | |> > ||----------------------------------------------------------- > | |> |---------- > | |> |To unsubscribe, e-mail: > |[EMAIL PROTECTED] > | |> |For additional commands, e-mail: > |[EMAIL PROTECTED] > | |> | > | |> | > | |> > ||----------------------------------------------------------- > | |> |---------- > | |> |To unsubscribe, e-mail: > |[EMAIL PROTECTED] > | |> |For additional commands, e-mail: > |[EMAIL PROTECTED] > | |> | > | |> > | |> > | |----------------------------------------------------------- > | |---------- > | |> To unsubscribe, e-mail: [EMAIL PROTECTED] > | |> For additional commands, e-mail: > [EMAIL PROTECTED] > | |> > | |> > | | > | |----------------------------------------------------------- > | |---------- > | |To unsubscribe, e-mail: [EMAIL PROTECTED] > | |For additional commands, e-mail: [EMAIL PROTECTED] > | | > | > |----------------------------------------------------------- > |---------- > |To unsubscribe, e-mail: [EMAIL PROTECTED] > |For additional commands, e-mail: [EMAIL PROTECTED] > | > | > |----------------------------------------------------------- > |---------- > |To unsubscribe, e-mail: [EMAIL PROTECTED] > |For additional commands, e-mail: [EMAIL PROTECTED] > | > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
