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 Sergey Zolotaryov <an...@gmail.com> on 2006/08/09 13:43:14 UTC
Results portion
Is there a way to retrieve a portion of results using Derby? For
example, I want to get rows from 10 to 20 from a resultset. All dbs I
know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
Also is there a way to retrieve the total number of rows in last
select?
Thank you.
Re: Results portion
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Hi, guys.
I already responded to this kind of thread some times... You could
search archives, you will find lots of messages (from others too).
But lets go:
If you are using Object persistence, please take a look at:
http://java.sun.com/blueprints/corej2eepatterns/Patterns/ValueListHandler.html
If you are using plain JDBC, you could:
1) Execute a query on PK only. Keep result as Vector (uses much less
memory than ArrayList) in memory (or in session, in case of a java web
server), something like:
select ID from PRODUCTS
2) When you need to show a page, take number of PKs you need, and
execute a IN query.
select * from PRODUCTS where ID in (id1, id2, id3, id4, id5...id20)
I made this on tables with 1.000.000 records on a 512Mb VM using "int"
PK without any problem.
If you will use session, you should take care with:
1) New and deleted records could/should invalidate session (you must
define your policy)
2) If you have several users working on same table all the time, put
vector on application area (accessible by all the users). You'll avoid
lot's of memory consumption.
Both method above (OO/Jdbc based) guarantees you are always with most
recent data on screen.
Additional advantage: works with all databases in the market.
Disvantage: if you want to ORDER your records, then you need to add more
fields to query (some databases need ORDER field appear on SELECT
clause. AFAIK Derby too).
Regards,
Richter
Fantry, John escreveu:
> Using a scrollable cursor is a possibility. However, you incur a
> performance penalty, which in my experience, is quite severe if your
> result set is very large. Let's say you need to move the cursor to the
> last row in the result set. Derby will iterate through all the rows in
> the result set from the current position of the cursor until the last
> row is reached. In my application I felt that the amount of time the
> user was forced to wait while this took place was unacceptable.
>
> What Sergey, and several others would like to be able to do, is use a
> LIMIT command with an OFFSET modifier which will allow you to generate a
> result set that only has rows 10 - 20 in it. The result set does not
> contain rows 1 - 9, nor does it contain any rows after 20. This is what
> Sergey is asking for. This behavior is possible in databases like MySQL
> and Postgres, but of course those are not Java based embedded
> implementations.
>
> So, Sergey, if you execute your query using a scrollable cursor you can
> move the cursor to your desired offset and then read the desired number
> or rows (in this case you'll probably want to call setMaxRows() on your
> Statement object so you don't retrieve any additional rows that you
> don't need). You may find this to be perfectly acceptable for your
> needs. This approach didn't work well for me due to the rather large
> size of my result set.
>
> -----Original Message-----
> From: Michael Segel [mailto:msegel@segel.com]
>
> Sent: Wednesday, August 09, 2006 1:00 PM
> To: 'Derby Discussion'
> Subject: RE: Results portion
>
> Hmmm...
>
> I realize its been a while since I've read Derby's manual, but doesn't
> Derby
> support Scrollable cursors?
>
> A scrollable cursor will allow you to run a query and to fetch specific
> rows
> from the result set. At a minimum, that is what Sergey is originally
> asking
> and which is supported by multiple RDBMSs.
>
>
>
>> -----Original Message-----
>> From: Fantry, John [mailto:JFantry@analogic.com]
>> Sent: Wednesday, August 09, 2006 10:00 AM
>> To: Derby Discussion
>> Subject: RE: Results portion
>>
>>
>
>
>
>
>> This question has bean asked several times already . . . Once by me
>>
> ;-)
>
>> The answer is NO. There is no way to retrieve rows in this fashion.
>>
> I
>
>> had to create an extra "index" column in my table in order to simulate
>> this kind of behavior. Big pain in the . . .
>>
>>
>
>
>
>
>> -----Original Message-----
>> From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
>>
>>
>
>
>> Sent: Wednesday, August 09, 2006 7:43 AM
>> To: derby-user@db.apache.org
>> Subject: Results portion
>>
>>
>
>
>> Is there a way to retrieve a portion of results using Derby? For
>> example, I want to get rows from 10 to 20 from a resultset. All dbs I
>> know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
>> Also is there a way to retrieve the total number of rows in last
>> select?
>> Thank you.
>>
>>
>
>
>> ****************************************************************
>> 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.
>>
>
>
>
> ****************************************************************
> 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: Results portion
Posted by Paul J DeCoursey <pa...@decoursey.net>.
I see this question all the time... and although I wish that we could
just get a LIMIT command. The answer is simple, when you do your query
only fetch the primary key and use a Scrollable cursor. Since there is
only one column in each row the performance penalty is small. Then you
get the keys of the rows you want and execute the full query based on
those keys. If I had more time, I could provide an example... but I know
there are a few in the list archives.
Paul
Fantry, John wrote:
> Using a scrollable cursor is a possibility. However, you incur a
> performance penalty, which in my experience, is quite severe if your
> result set is very large. Let's say you need to move the cursor to the
> last row in the result set. Derby will iterate through all the rows in
> the result set from the current position of the cursor until the last
> row is reached. In my application I felt that the amount of time the
> user was forced to wait while this took place was unacceptable.
>
> What Sergey, and several others would like to be able to do, is use a
> LIMIT command with an OFFSET modifier which will allow you to generate a
> result set that only has rows 10 - 20 in it. The result set does not
> contain rows 1 - 9, nor does it contain any rows after 20. This is what
> Sergey is asking for. This behavior is possible in databases like MySQL
> and Postgres, but of course those are not Java based embedded
> implementations.
>
> So, Sergey, if you execute your query using a scrollable cursor you can
> move the cursor to your desired offset and then read the desired number
> or rows (in this case you'll probably want to call setMaxRows() on your
> Statement object so you don't retrieve any additional rows that you
> don't need). You may find this to be perfectly acceptable for your
> needs. This approach didn't work well for me due to the rather large
> size of my result set.
>
> -----Original Message-----
> From: Michael Segel [mailto:msegel@segel.com]
>
> Sent: Wednesday, August 09, 2006 1:00 PM
> To: 'Derby Discussion'
> Subject: RE: Results portion
>
> Hmmm...
>
> I realize its been a while since I've read Derby's manual, but doesn't
> Derby
> support Scrollable cursors?
>
> A scrollable cursor will allow you to run a query and to fetch specific
> rows
> from the result set. At a minimum, that is what Sergey is originally
> asking
> and which is supported by multiple RDBMSs.
>
>
>
>> -----Original Message-----
>> From: Fantry, John [mailto:JFantry@analogic.com]
>> Sent: Wednesday, August 09, 2006 10:00 AM
>> To: Derby Discussion
>> Subject: RE: Results portion
>>
>>
>
>
>
>
>> This question has bean asked several times already . . . Once by me
>>
> ;-)
>
>> The answer is NO. There is no way to retrieve rows in this fashion.
>>
> I
>
>> had to create an extra "index" column in my table in order to simulate
>> this kind of behavior. Big pain in the . . .
>>
>>
>
>
>
>
>> -----Original Message-----
>> From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
>>
>>
>
>
>> Sent: Wednesday, August 09, 2006 7:43 AM
>> To: derby-user@db.apache.org
>> Subject: Results portion
>>
>>
>
>
>> Is there a way to retrieve a portion of results using Derby? For
>> example, I want to get rows from 10 to 20 from a resultset. All dbs I
>> know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
>> Also is there a way to retrieve the total number of rows in last
>> select?
>> Thank you.
>>
>>
>
>
>> ****************************************************************
>> 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.
>>
>
>
>
> ****************************************************************
> 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: Results portion
Posted by "Fantry, John" <JF...@analogic.com>.
Using a scrollable cursor is a possibility. However, you incur a
performance penalty, which in my experience, is quite severe if your
result set is very large. Let's say you need to move the cursor to the
last row in the result set. Derby will iterate through all the rows in
the result set from the current position of the cursor until the last
row is reached. In my application I felt that the amount of time the
user was forced to wait while this took place was unacceptable.
What Sergey, and several others would like to be able to do, is use a
LIMIT command with an OFFSET modifier which will allow you to generate a
result set that only has rows 10 - 20 in it. The result set does not
contain rows 1 - 9, nor does it contain any rows after 20. This is what
Sergey is asking for. This behavior is possible in databases like MySQL
and Postgres, but of course those are not Java based embedded
implementations.
So, Sergey, if you execute your query using a scrollable cursor you can
move the cursor to your desired offset and then read the desired number
or rows (in this case you'll probably want to call setMaxRows() on your
Statement object so you don't retrieve any additional rows that you
don't need). You may find this to be perfectly acceptable for your
needs. This approach didn't work well for me due to the rather large
size of my result set.
-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com]
Sent: Wednesday, August 09, 2006 1:00 PM
To: 'Derby Discussion'
Subject: RE: Results portion
Hmmm...
I realize its been a while since I've read Derby's manual, but doesn't
Derby
support Scrollable cursors?
A scrollable cursor will allow you to run a query and to fetch specific
rows
from the result set. At a minimum, that is what Sergey is originally
asking
and which is supported by multiple RDBMSs.
> -----Original Message-----
> From: Fantry, John [mailto:JFantry@analogic.com]
> Sent: Wednesday, August 09, 2006 10:00 AM
> To: Derby Discussion
> Subject: RE: Results portion
>
>
> This question has bean asked several times already . . . Once by me
;-)
> The answer is NO. There is no way to retrieve rows in this fashion.
I
> had to create an extra "index" column in my table in order to simulate
> this kind of behavior. Big pain in the . . .
>
>
> -----Original Message-----
> From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
>
> Sent: Wednesday, August 09, 2006 7:43 AM
> To: derby-user@db.apache.org
> Subject: Results portion
>
> Is there a way to retrieve a portion of results using Derby? For
> example, I want to get rows from 10 to 20 from a resultset. All dbs I
> know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
> Also is there a way to retrieve the total number of rows in last
> select?
> Thank you.
>
> ****************************************************************
> 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.
****************************************************************
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: Results portion
Posted by Michael Segel <ms...@segel.com>.
Hmmm...
I realize its been a while since I've read Derby's manual, but doesn't Derby
support Scrollable cursors?
A scrollable cursor will allow you to run a query and to fetch specific rows
from the result set. At a minimum, that is what Sergey is originally asking
and which is supported by multiple RDBMSs.
> -----Original Message-----
> From: Fantry, John [mailto:JFantry@analogic.com]
> Sent: Wednesday, August 09, 2006 10:00 AM
> To: Derby Discussion
> Subject: RE: Results portion
>
>
> This question has bean asked several times already . . . Once by me ;-)
> The answer is NO. There is no way to retrieve rows in this fashion. I
> had to create an extra "index" column in my table in order to simulate
> this kind of behavior. Big pain in the . . .
>
>
> -----Original Message-----
> From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
>
> Sent: Wednesday, August 09, 2006 7:43 AM
> To: derby-user@db.apache.org
> Subject: Results portion
>
> Is there a way to retrieve a portion of results using Derby? For
> example, I want to get rows from 10 to 20 from a resultset. All dbs I
> know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
> Also is there a way to retrieve the total number of rows in last
> select?
> Thank you.
>
> ****************************************************************
> 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: Results portion
Posted by "Fantry, John" <JF...@analogic.com>.
This question has bean asked several times already . . . Once by me ;-)
The answer is NO. There is no way to retrieve rows in this fashion. I
had to create an extra "index" column in my table in order to simulate
this kind of behavior. Big pain in the . . .
-----Original Message-----
From: Sergey Zolotaryov [mailto:anydoby@gmail.com]
Sent: Wednesday, August 09, 2006 7:43 AM
To: derby-user@db.apache.org
Subject: Results portion
Is there a way to retrieve a portion of results using Derby? For
example, I want to get rows from 10 to 20 from a resultset. All dbs I
know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
Also is there a way to retrieve the total number of rows in last
select?
Thank you.
****************************************************************
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.