Hi,
Thank you for your quick reply.
I will elaborate a little on my question:
I often need to retrieve a large amount of data from a remote MySQL
database. However,
if my application runs with a default heap size, then i will quickly get
some sort of heap space
exception. The reason is that the ResultSet containing the retrieved
data is too large. What i have
done to get around this is to stream the results from the database, and
process rows one by one
as they are streamed (for instance, storing them in a local database,
like Derby). Of course, things
are most likely behaving more optimal than only transfering one row at a
time from the database
(yes, some buffers are most likely involved). However, my key point was
that i do not have to wait
for the entire ResultSet to become ready before i can start iterating
over the rows. Instead, rows (
be it one or hundred) are retrieved as i iterate over the ResultSet.
So, my question is wether the Derby Driver has this ability too?
Kind regards,
Stian Brattland
My intention with the question was not really to point out that a the
driver needs to retrive
results in the most ineffective manner as possible.
Bernt M. Johnsen skrev:
Hi,
Some general remarks (don't remember the exact details of what Derby
actually does with setFetchSize).
Stian Brattland wrote (2010-01-14 08:43:32):
Hi,
I've got a question regarding results streaming. The J/Connector for
MySQL supports results streaming, which means
that you can stream and process rows in a ResultSet one by one.
Normally, all rows in a ResultSet will be retrived
before you can process the ResultSet. However, i am curious as to wether
this "feature" also exists in Derby?
Normally, a JDBC driver will retrieve a suitable number of rows, not
necessarily all, depending on various factors such as row size, number
of rows resulting from the query and communication buffer size.
In MySQL, you would do the following to stream results from the database
as you iterate through a ResultSet:
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
setFetchSize is just a hint to the driver, See
http://jscstage.sfbay.sun.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)
A well written driver will still try to do thing's optimal, such as
e.g. fill up the communication buffer with rows to reduce the number
of roundtrips, regardless of how low you set the fetchSize.
And last, why would you like to force the driver to fetch the rows one
by one? The only thing you will get fromthat, is extra overhead.
