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 15:59:10 GMT
Hi again,

>>>>>>>>>>>> Stian Brattland wrote (2010-01-14 14:42:47):
> Hi,
> Thank you for your answer Knut Anders. I do understand from what you are  
> saying that the Derby Driver
> never will (to user the words of Brett) "materialize the entire  
> ResultSet on the client-side before returning it
> to the user", but only materialize as much data as the communications  
> buffer can hold. That's fine. However,
> imagine the following scenario:
> I run a query on a Derby Database which will return 1 million rows. The  
> Derby Driver initially only returns
> as much data as the communications buffer can hold. However, as i start  
> iterating over each row in the
> ResultSet, the Derby Driver naturally needs to retrieve the rows i am  
> iterating over. Now, when i am on the
> last row in the ResultSet, wouldn't that mean that the entire ResultSet  
> is loaded into memory?

No. Derby ResultSets are by default TYPE_FORWARD_ONLY and
CONCUR_READ_ONLY, so that will not happen. The buffer will be reused
for new "chunks" of data.

> What MySQL allows me to do is to run the query in a "streaming mode"  
> (yes, might be a stupid name, but it's
> a good description of what it actually does), which essentially only  
> allows me to iterate forward in the
> ResultSet. In other words, an exception is thrown if i try to invoke the  
> previous() method on the java.sql.ResultSet
> interface (as it does not store the data i have already iterated over in  
> the ResultSet). I do not know the inner workings
> of the MySQL driver, but i assume that data for rows which i already  
> does not get stored in the ResultSet, but is
> rather garbage collected. Thus, i will never run into a  
> java.lang.OutOfMemoryException as the driver does not require
> an ever increasing amount of memory to store the retrieved rows in a  
> ResultSet.
> http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html
> I have to admit that i don't really care that much about the inner  
> workings of the driver, as long as i do not
> receive an java.lang.OutOfMemoryException after having iterated over  
> 100.000 rows in a ResultSet.

You won't :-)

> Kind regards,
> Stian
> Knut Anders Hatlen skrev:
>> Stian Brattland <stian@brattland.no> writes:
>>> Yes!
>>> Thank you Brett. That is excactly what i've been trying to
>>> explain. That's the feature i've been asking about.
>> Hi Stian,
>> What Brett described is exactly how Derby behaves by default (except, as
>> Bernt mentioned, we pre-fetch as many rows as we can in a 32K
>> communications buffer, not some constant number of rows). Are you seeing
>> a different behaviour in your application?

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

View raw message