The main difference is that my solution doesn't return the whole result set
from the DB, only the page you're interested in, which might be an issue if,
say, you're query selects 100,000 rows in total but you only want to see
pages of 50. But the downside is that I think it'll only work on Oracle
('rownum' is Oracle-specific, I think!).
Regards,
Al.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: 22 November 2002 13:26
> To: JDJList
> Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
>
>
>
> 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 change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
