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
____________________________________________________
