Does derby support "server-side cursors" in embedded mode?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Does derby support "server-side cursors" in embedded mode?

Johann Petrak
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)?
* Can derby deal with the statement.setFetchSize(n) method to actually limit the number of rows getting fetched at once?

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!
Reply | Threaded
Open this post in threaded view
|

Re: Does derby support "server-side cursors" in embedded mode?

Rick Hillegas-3
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,
-Rick
>
> 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!