Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Message-ID: <4B4EEB87.5070301@brattland.no>
Date: Thu, 14 Jan 2010 11:01:43 +0100
From: Stian Brattland <stian@brattland.no>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Streaming Results
References: <4B4ECB24.4020607@brattland.no>
 <20100114090801.GA6588@atum01.Norway.Sun.COM>
In-Reply-To: <20100114090801.GA6588@atum01.Norway.Sun.COM>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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