Yes!
Thank you Brett. That is excactly what i've been trying to explain.
That's the feature i've been asking about.
Kind regards,
Stian
Brett Wooldridge skrev:
Bernt,
I think the issue is that Derby will materialize the entire ResultSet
on the client-side before returning it to the user. If the ResultSet
is one million rows, then one million rows will be transferred and
materialized on the client before the executeQuery() call returns to
the user.
Some databases and drivers have the capability to return a streaming
ResultSet, such that rows are only transferred as ResultSet.next() is
called. If the driver is clever, it can keep a bit ahead of the calls
to next() by transferring X number of rows at a time (where X is
something much smaller than a million).
I do not have personal knowledge of whether Derby supports ResultSet
streaming (which is distinct from streaming datatypes like CLOBs or
BLOBs). I just wanted to make sure the distinction in the question
was clear.
Brett
On Thu, Jan 14, 2010 at 7:33 PM, Bernt M. Johnsen
<[email protected] <mailto:[email protected]>> wrote:
>>>>>>>>>>>> Stian Brattland wrote (2010-01-14 11:01:43):
> 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?
Yes, that is exactly what the Derby driver (and all other reasonable
implemented JDBC drivers) will do. The driver will attempt to fill up
the communication buffer (32K) as long as at least one row fits into
it.
>
> 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)
<http://jscstage.sfbay.sun.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize%28int%29>
>>
>> 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.
>>
--
Bernt Marius Johnsen, Staff Engineer
Database Technology Group, Sun Microsystems, Trondheim, Norway
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFLTvL+lFBD9TXBAPARAvlGAJ9zaW2WAFG/97gneqpYZq8IWAkfagCfVEH9
3kGYBcg23Fbt34k9lSiqOjk=
=D9VP
-----END PGP SIGNATURE-----
