You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Fantry, John" <JF...@analogic.com> on 2006/07/19 20:24:28 UTC
Paginating result sets
I need some help figuring how to paginate result sets with Derby. I
have scanned the list archives and have found one similar thread that
never received a complete answer from May 2005.
>From what I understand this pagination behavior is possible in MySQL by
using the 'LIMIT' query. This will allow me to select the first 100
rows, the second 100 rows, or any arbitrary chunk of rows that may be
part of the result set.
I understand that Derby does not support the LIMIT query. I also
understand that the JDBC Statement class has two methods that are
related (setFetchSize() and setMaxRows()), but they do not support the
pagination behavior I desire.
So let's say that a given SELECT statement will return 1000 rows. How
do I create a SELECT statement that will only return me rows 600 - 699?
Having spent a lot of time searching through Derby docs I have concluded
that this is not possible in Derby, but I am hoping I am wrong.
Does anyone know how to do this, or do I have to continue my search for
an embedded database that supports LIMIT?
Thanks,
-John
****************************************************************
The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to DeliveryErrors@analogic.com - and destroy all copies of this information, including any attachments, without reading or disclosing them.
Thank you.
Re: Paginating result sets
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
I have paginated million rows table using ValueListHandler design pattern:
http://java.sun.com/blueprints/patterns/ValueListHandler.html
The kernel is you load all PK for every record, then load only records
for PKs that will fill in a page.
When user request next/previous page, just take a bunch of new PKs and
load respective records.
Very, very fast, and easy to implement (even if you are not using OO
persistence).
Regards,
Richter
Fantry, John escreveu:
> 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.
>
> 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.
>
> -John
>
> -----Original Message-----
> From: Michael Segel [mailto:derby@segel.com]
>
> Sent: Wednesday, July 19, 2006 2:54 PM
> To: Derby Discussion
> Subject: Re: Paginating result sets
>
> Did you try to use a scrollable cursor?
> Or how about storing your result set in to a temp table with some sort
> of row
>
> id?
>
> Both of these solutions are pretty much database independent, assuming
> of
>
> course that they support the concept of cursors...
>
> If you're going to use either solution, you're also going to need to
> consider
>
> what happens when you time out the user and you need to free up
> resources.
>
> HTH
>
> -G
>
> On Wednesday 19 July 2006 1:24 pm, Fantry, John wrote:
>
>> I need some help figuring how to paginate result sets with Derby. I
>> have scanned the list archives and have found one similar thread that
>> never received a complete answer from May 2005.
>>
>> From what I understand this pagination behavior is possible in MySQL
>>
> by
>
>> using the 'LIMIT' query. This will allow me to select the first 100
>> rows, the second 100 rows, or any arbitrary chunk of rows that may be
>> part of the result set.
>>
>> I understand that Derby does not support the LIMIT query. I also
>> understand that the JDBC Statement class has two methods that are
>> related (setFetchSize() and setMaxRows()), but they do not support the
>> pagination behavior I desire.
>>
>> So let's say that a given SELECT statement will return 1000 rows. How
>> do I create a SELECT statement that will only return me rows 600 -
>>
> 699?
>
>> Having spent a lot of time searching through Derby docs I have
>>
> concluded
>
>> that this is not possible in Derby, but I am hoping I am wrong.
>>
>> Does anyone know how to do this, or do I have to continue my search
>>
> for
>
>> an embedded database that supports LIMIT?
>>
>> Thanks,
>> -John
>>
>>
>> ****************************************************************
>> The information transmitted in this message is confidential and may be
>> privileged. Any review, retransmission, dissemination, or other use
>>
> of
>
>> this information by persons or entities other than the intended
>>
> recipient
>
>> is prohibited. If you are not the intended recipient, please notify
>> Analogic Corporation immediately - by replying to this message or by
>> sending an email to DeliveryErrors@analogic.com - and destroy all
>>
> copies of
>
>> this information, including any attachments, without reading or
>>
> disclosing
>
>> them.
>>
>> Thank you.
>>
>
> --
>
> --
> Michael Segel
> Principal
>
> Michael Segel Consulting Corp.
> derby@segel.com
> (312) 952-8175 [mobile]
>
> ****************************************************************
> The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to DeliveryErrors@analogic.com - and destroy all copies of this information, including any attachments, without reading or disclosing them.
>
> Thank you.
>
>
>
>
Re: Paginating result sets
Posted by Andreas Korneliussen <An...@Sun.COM>.
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.
Regards
Andreas
R: Paginating result sets
Posted by Flavio Palumbo <f....@silmasoftware.com>.
Hi,
I tested, briefly, these stetements on DB2/400 and it works fine (I found it
on a book, never used it in production) :
select * from (
select * from (
select * from eurofile/anatit
fetch first 35 rows only) as t1
order by atcodi desc fetch first 5 rows only) as t2
order by atcodi
as you increment the number of rows in the inner select (35) you move
forward the cursor on the table, viewing always 5 rows ( well I'm not a SQL
guru ... I think tou can understand the code better of me).
HTH
Flavio
> -----Messaggio originale-----
> Da: Fantry, John [mailto:JFantry@analogic.com]
> Inviato: mercoledì 19 luglio 2006 21.01
> A: Derby Discussion; derby@segel.com
> Oggetto: RE: Paginating result sets
>
>
>
> 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.
>
> 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.
>
> -John
>
> -----Original Message-----
> From: Michael Segel [mailto:derby@segel.com] Sent: Wednesday,
> July 19, 2006 2:54 PM
> To: Derby Discussion
> Subject: Re: Paginating result sets
>
> Did you try to use a scrollable cursor?
> Or how about storing your result set in to a temp table with some sort
> of row id?
>
> Both of these solutions are pretty much database independent, assuming
> of course that they support the concept of cursors...
>
> If you're going to use either solution, you're also going to need to
> consider what happens when you time out the user and you need to free up
> resources.
>
> HTH
>
> -G
>
> On Wednesday 19 July 2006 1:24 pm, Fantry, John wrote:
> > I need some help figuring how to paginate result sets with Derby. I
> > have scanned the list archives and have found one similar thread that
> > never received a complete answer from May 2005.
> >
> > From what I understand this pagination behavior is possible in MySQL
> by
> > using the 'LIMIT' query. This will allow me to select the first 100
> > rows, the second 100 rows, or any arbitrary chunk of rows that may be
> > part of the result set.
> >
> > I understand that Derby does not support the LIMIT query. I also
> > understand that the JDBC Statement class has two methods that are
> > related (setFetchSize() and setMaxRows()), but they do not support the
> > pagination behavior I desire.
> >
> > So let's say that a given SELECT statement will return 1000 rows. How
> > do I create a SELECT statement that will only return me rows 600 -
> 699?
> > Having spent a lot of time searching through Derby docs I have
> concluded
> > that this is not possible in Derby, but I am hoping I am wrong.
> >
> > Does anyone know how to do this, or do I have to continue my search
> for
> > an embedded database that supports LIMIT?
> >
> > Thanks,
> > -John
> >
> >
> > ****************************************************************
> > The information transmitted in this message is confidential and may be
> > privileged. Any review, retransmission, dissemination, or other use
> of
> > this information by persons or entities other than the intended
> recipient
> > is prohibited. If you are not the intended recipient, please notify
> > Analogic Corporation immediately - by replying to this message or by
> > sending an email to DeliveryErrors@analogic.com - and destroy all
> copies of
> > this information, including any attachments, without reading or
> disclosing
> > them.
> >
> > Thank you.
>
> -- --
> Michael Segel
> Principal Michael Segel Consulting Corp.
> derby@segel.com
> (312) 952-8175 [mobile]
>
> ****************************************************************
> The information transmitted in this message is confidential and
> may be privileged. Any review, retransmission, dissemination, or
> other use of this information by persons or entities other than
> the intended recipient is prohibited. If you are not the
> intended recipient, please notify Analogic Corporation
> immediately - by replying to this message or by sending an email
> to DeliveryErrors@analogic.com - and destroy all copies of this
> information, including any attachments, without reading or
> disclosing them.
>
> Thank you.
>
-----------------------------------------------------------
Il presente messaggio non costituisce un impegno contrattuale tra SILMA S.r.l. ed il destinatario.
Le opinioni ivi espresse sono quelle dell'autore.
SILMA S.r.l. non assume alcuna responsabilita riguardo al contenuto del presente messaggio.
Il messaggio è destinato esclusivamente al destinatario.
Il contenuto e gli allegati sono da considerarsi di natura confidenziale
Nel caso abbiate ricevuto il presente messaggio per errore siete pregati di comunicarlo
alla casella segreteria@silmasoftware.com.
RE: Paginating result sets
Posted by "Fantry, John" <JF...@analogic.com>.
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.
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.
-John
-----Original Message-----
From: Michael Segel [mailto:derby@segel.com]
Sent: Wednesday, July 19, 2006 2:54 PM
To: Derby Discussion
Subject: Re: Paginating result sets
Did you try to use a scrollable cursor?
Or how about storing your result set in to a temp table with some sort
of row
id?
Both of these solutions are pretty much database independent, assuming
of
course that they support the concept of cursors...
If you're going to use either solution, you're also going to need to
consider
what happens when you time out the user and you need to free up
resources.
HTH
-G
On Wednesday 19 July 2006 1:24 pm, Fantry, John wrote:
> I need some help figuring how to paginate result sets with Derby. I
> have scanned the list archives and have found one similar thread that
> never received a complete answer from May 2005.
>
> From what I understand this pagination behavior is possible in MySQL
by
> using the 'LIMIT' query. This will allow me to select the first 100
> rows, the second 100 rows, or any arbitrary chunk of rows that may be
> part of the result set.
>
> I understand that Derby does not support the LIMIT query. I also
> understand that the JDBC Statement class has two methods that are
> related (setFetchSize() and setMaxRows()), but they do not support the
> pagination behavior I desire.
>
> So let's say that a given SELECT statement will return 1000 rows. How
> do I create a SELECT statement that will only return me rows 600 -
699?
> Having spent a lot of time searching through Derby docs I have
concluded
> that this is not possible in Derby, but I am hoping I am wrong.
>
> Does anyone know how to do this, or do I have to continue my search
for
> an embedded database that supports LIMIT?
>
> Thanks,
> -John
>
>
> ****************************************************************
> The information transmitted in this message is confidential and may be
> privileged. Any review, retransmission, dissemination, or other use
of
> this information by persons or entities other than the intended
recipient
> is prohibited. If you are not the intended recipient, please notify
> Analogic Corporation immediately - by replying to this message or by
> sending an email to DeliveryErrors@analogic.com - and destroy all
copies of
> this information, including any attachments, without reading or
disclosing
> them.
>
> Thank you.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]
****************************************************************
The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to DeliveryErrors@analogic.com - and destroy all copies of this information, including any attachments, without reading or disclosing them.
Thank you.
Re: Paginating result sets
Posted by Michael Segel <de...@segel.com>.
Did you try to use a scrollable cursor?
Or how about storing your result set in to a temp table with some sort of row
id?
Both of these solutions are pretty much database independent, assuming of
course that they support the concept of cursors...
If you're going to use either solution, you're also going to need to consider
what happens when you time out the user and you need to free up resources.
HTH
-G
On Wednesday 19 July 2006 1:24 pm, Fantry, John wrote:
> I need some help figuring how to paginate result sets with Derby. I
> have scanned the list archives and have found one similar thread that
> never received a complete answer from May 2005.
>
> From what I understand this pagination behavior is possible in MySQL by
> using the 'LIMIT' query. This will allow me to select the first 100
> rows, the second 100 rows, or any arbitrary chunk of rows that may be
> part of the result set.
>
> I understand that Derby does not support the LIMIT query. I also
> understand that the JDBC Statement class has two methods that are
> related (setFetchSize() and setMaxRows()), but they do not support the
> pagination behavior I desire.
>
> So let's say that a given SELECT statement will return 1000 rows. How
> do I create a SELECT statement that will only return me rows 600 - 699?
> Having spent a lot of time searching through Derby docs I have concluded
> that this is not possible in Derby, but I am hoping I am wrong.
>
> Does anyone know how to do this, or do I have to continue my search for
> an embedded database that supports LIMIT?
>
> Thanks,
> -John
>
>
> ****************************************************************
> The information transmitted in this message is confidential and may be
> privileged. Any review, retransmission, dissemination, or other use of
> this information by persons or entities other than the intended recipient
> is prohibited. If you are not the intended recipient, please notify
> Analogic Corporation immediately - by replying to this message or by
> sending an email to DeliveryErrors@analogic.com - and destroy all copies of
> this information, including any attachments, without reading or disclosing
> them.
>
> Thank you.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]