db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Korneliussen <Andreas.Kornelius...@Sun.COM>
Subject Re: Paginating result sets
Date Thu, 20 Jul 2006 11:12:28 GMT
Fantry, John wrote:
> I have tried a scrollable cursor and it is waaaaaaaaay too slow.  The
> 'absolute()' method on the ResultSet object takes so long to return I
> have to show an hour glass icon and the user has to wait an eternity.
> In this case the result set had over 2 million rows.  I have a
> requirement to support a huge amount of data, so I'm trying to find a
> solution that performs better.  I could try a different database like
> PostgreSQL, but I prefer an embedded solution.

If you call ResultSet.absolute(n) in Derby, Derby will read all rows 
from 1..n and put them into a temporary table, unless they have already 
been backed into the temprorary table by previous calls on the ResultSet 
(i.e like ResultSet.last() which would scan through all rows and back 
them to the temporary table).

If the rows have already been backed, ResultSet.absolute(n) should be 
just as fast as looking up a single, indexed record.  So, if your 
application tries to first access the first 100 records, then the next 
200 records, I do not see that you would need to use a hour glass to 
wait for Derby. If it on the other hand tries to read the last record 
first, you will have to wait for Derby to read through all the rows (and 
back them to the temporary table) to find the last row.

> I have considered using a temp table with row id as you've suggested,
> but haven't tried it yet.  I'm guessing the performance will be poor,
> but there is only one way to find out.

Internally, scrollable result sets use a temp table indexed on row 
number in the result set.


View raw message