db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Does derby support "server-side cursors" in embedded mode?
Date Tue, 08 Nov 2016 01:00:40 GMT
Hi Johann,

Some responses inline...

On 11/7/16, 7:16 AM, Johann Petrak wrote:
> What I would like to do is this: use Derby in embedded mode to create a
> very large table with fields "key" and "value":
> create table mytable (
>   key varchar(100) not null,
>   value varchar not null)
> and a non-unique index on field key.
> This table would get filled with about 100 million rows where each value
> could be several thousand characters.
> Then, I would need to query the database to select all rows in order 
> of keys,
> like this:
>   SELECT key,value from tab order by key
> The result set would get processed by repeatedly calling next.
> Now here are my questions:
> * can derby do this without putting the complete result into memory 
> first (like other embedded Java databases do)?
Since you are creating an index on the ORDER BY column, I would expect 
that a dataset of this size would cause Derby to read the index from 
start to finish, probing, as it goes, into the heap to retrieve the 
value column. I would not expect that Derby would toss any rows into a 
sorter. You might fill up Derby's page cache, but you would not need to 
throw extra heap at Derby in order to get this experiment to succeed.
> * Can derby deal with the statement.setFetchSize(n) method to actually 
> limit the number of rows getting fetched at once?
The embedded Derby JDBC driver implements both Statement.setFetchSize() 
and ResultSet.setFetchSize().

Hope this helps,
> My main concern is if it is possible to deal with the result set of 
> that query, where I have 100 million rows, and
> each row has considerable size, without needing a lot of heap space or 
> RAM: ideally I would only need the RAM needed
> to hold a couple of hundred or thousand result rows in memory.
> Thanks a lot!

View raw message