db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Edson Carlos Ericksson Richter <edson.rich...@mgrinformatica.com.br>
Subject Re: Results portion
Date Wed, 09 Aug 2006 18:21:02 GMT
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.
>
>
>
>   


Mime
View raw message