db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Streaming Results
Date Thu, 14 Jan 2010 10:33:35 GMT
>>>>>>>>>>>> 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

> 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
>>> 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.

Bernt Marius Johnsen, Staff Engineer
Database Technology Group, Sun Microsystems, Trondheim, Norway

View raw message