I have one suggestion. If you don't want your solution to be vendor
specific from java application point of view, It would be a good idea to
implement a stored procedure to return a specified no of rows
> I have also found that my original solution performs well regardless of
> how
> big the result set is... Granted if it returns say 5000 rows then you
> might
> see an initial hit to bring all of it back however on subsequent requests
> to the database it is fast as the original result set will be cached by
> the
> RDMS. This works for both Oracle and DB2 as those are the two main systems
> we use here. I would imagine the behaviour would be the same for any other
> major RDMS such as M$ SQLServer, etc.
>
> Please keep in mind that my solution has been in production for the last
> three years with no issues reported so it does work well...
>
> Greg.
>
>
>
>
>
> [EMAIL PROTECTED] on 2002/11/22 04:53:35 AM
>
> Please respond to "JDJList" <[EMAIL PROTECTED]>
>
> To: "JDJList" <[EMAIL PROTECTED]>
> cc:
>
> Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
>
>
> It is possible to get round this problem with a query of the following
> form:
>
>
> select c1 username, c2 fullname, c3 jobtitle, c4 totalcount from (
> select c1, c2, c3, c4, rownum as rowindex from (
> select u.username c1,
> u.surname c2,
> u.jobtitle c3,
> count(*) over() c4
> from users u,
> where <<someClause>>
> order by <<sortColIndex>>;
> )) where (rowindex > <<startIndex>>) and (rownum <=
> <<resultPageSize>>);
>
> So the inner select gets *all* the matching results plus the total count,
> sorted by the desired column; the next select additionally gets the rownum
> into rowindex; then the outer select gets just the page of results you
> want,
> retrieving the first <<resultPageSize>> rows from <<startIndex>>. Note
> that
> since the 'order by' is on the inner query, it doesn't suffer from the
> ordering problem that Greg mentioned.
>
> I was warned when I first employed this technique that because the inner
> query is selecting *all* matching rows (like Greg's original solution)
> that
> it would be slow, but I found that the full query performs quite well
> enough
> for me. If you have very large result sets then you might start to see
> problems, though I haven't tried.
>
> Al.
>
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > Sent: 22 November 2002 00:04
> > To: JDJList
> > Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
> >
> >
> >
> > Hi,
> >
> > Your revised solution is a good idea however it will not work
> > with an ORDER BY clause in the SQL statement. We use Oracle
> > here and originally that was what I wanted to do but I found
> > through much experimentation and consulting the Oracle docs
> > that Oracle will first return the rows up to and/or including
> > what was specified for the ROWNUM setting and THEN it does
> > the ORDER BY!
> >
> > So what ends up happening is you find yourself missing some
> > rows that you feel should be there such as when you run the
> > statement without the ROWNUM clause using the ORDER BY and vice-versa.
> >
> > You can confirm this behaviour by applying the ROWNUM and
> > ORDER BY clauses independent of each other or in different
> > combinations in the original SQL statement to see what I'm
> > trying to explain here.
> >
> > HTH,
> > Greg.
> >
> >
> > Don Brown <[EMAIL PROTECTED]> on 11/21/2002 04:18:46 PM
> >
> >
> > Please respond to "JDJList" <[EMAIL PROTECTED]>
> >
> >
> > To: "JDJList" <[EMAIL PROTECTED]>
> > cc:
> >
> >
> >
> > Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
> >
> >
> >
> >
> > This code looks similar to what I used. However, I'll make
> > one point about making it a bit quicker (at least in the
> > earlier pages).
> >
> > The code basically returns the entire resultset, and loops
> > through it, adding rows for the page in question to a vector
> > while ignoring the rows before and after those of interest.
> > Okay. ORACLE provides a pseudo column called ROWNUM which is
> > similar to, yet different from the LIMIT of other DBs like
> > PostgresQL. SQL to the effect of "SELECT <whatever> FROM
> > <whatever> WHERE <whatever> AND ROWNUM BETWEEN 100 and 120"
> > ****will not work****. It will always return 0 rows. However
> > it will work if you put only the upper limit on it. EG:
> > "SELECT <whatever> FROM <whatever> WHERE <whatever> AND
> > ROWNUM <= 120" will work fine.
> >
> > In other words, you need to loop and skip all rows before the
> > disired rows, but you don't need to return nor loop through
> > skipping the ones following.
> >
> > Similarily, you could change the while loop from:
> > -- <snip> ---
> > > while (rs.next()) {
> > > nNumRows++;
> > >
> > > if ( (nNumRows >= p_nStartRow) && (nNumRows <
> > > p_nStartRow + p_nMaxRows) ) {
> > > // Populate our transaction list objects
> > > TransactionList transactionlist = new
> > > TransactionList(rs.getString(3), rs.getString(5), rs.getInt(6),
> > > rs.getString(7), rs.getString(8),
> > > rs.getBigDecimal(9, 2), rs.getBigDecimal(10, 2),
> > > rs.getBigDecimal(11, 2), rs.getBigDecimal(12, 4),
> > > rs.getBigDecimal(13, 4), rs.getBigDecimal(14, 4),
> > > rs.getString(15), rs.getString(17),
> > > rs.getString(22),
> > > rs.getLong(1), rs.getString(19),
> > > rs.getString(23), rs.getString(24),
> > > rs.getString(25));
> > > vData.addElement(transactionlist);
> > > }
> > > }
> > --- <end snip> ----
> >
> > To:
> > _-- <snip> ---
> > > while (rs.next() && (nNumRows < p_nStartRow +
> > p_nMaxRows)) {
> > --- cut (rest of the loop is the same) -----
> >
> > to do the same thing without the pseudo column.
> >
> > Someone else had reply about a scrollable resultset which
> > might be more effiecient than this solution, but I have no
> > experience with that.
> >
> > Hope this helps,
> > Don Brown
> >
> >
> > On November 21, 2002 11:56 am, [EMAIL PROTECTED] wrote:
> > > Ok guys I'm giving you all a gift.... I had the same problem
> > understanding
> > > how PetStore does it and after reading much documentation on the
> > > design pattern and various other people's implementation of said
> > > pattern here is my interpretation of the Page-by-Page Iterator
> > > pattern...
> > >
> > > This is actually still being used in production for a project
> > > developed from three years ago so I know it works... Don't
> > give me a
> > > hard time
> > about
> > > using a Vector though... If you want to use an ArrayList instead by
> > > all means feel free to change it!
> > >
> > > Debug is a class I wrote for outputting values while
> > developing/unit
> > > testing so implement your own such as Log4J or whatever. I'ved also
> > removed
> > > the bulk of the SQL statement as it is somewhat proprietary but I
> > > think
> > you
> > > will get the drift...
> >
> > > --- <Code is in original reply from [EMAIL PROTECTED]> ---
> >
> > ____________________________________________________
> > To change your JDJList options, please visit:
> > http://www.sys-con.com/java/list.cfm
> >
> > Be respectful! Clean up
> > your posts before replying
> > ____________________________________________________
> >
> >
> >
> >
> > **************************************************************
> > ****************
> > This email message is confidential to the intended recipient
> > and may be legally privileged. It is intended solely for the
> > addressee. Access to this
> > email by anyone else is unauthorized. Any unauthorised use,
> > dissemination
> > of the information, or copying of this message is prohibited.
> > If you are
> > not the intended addressee, kindly notify the sender by return e-mail
> > and delete this message.
> > **************************************************************
> > ****************
> >
> >
> > ____________________________________________________
> > To change your JDJList options, please visit:
> > http://www.sys-con.com/java/list.cfm
> >
> > Be respectful! Clean up
> > your posts before replying
> > ____________________________________________________
> >
>
> ____________________________________________________
> To change your JDJList options, please visit:
> http://www.sys-con.com/java/list.cfm
>
> Be respectful! Clean up your posts before replying
> ____________________________________________________
>
>
>
>
>
>
>
> ____________________________________________________
> To change your JDJList options, please visit:
> http://www.sys-con.com/java/list.cfm
>
> Be respectful! Clean up your posts before replying
> ____________________________________________________
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
