db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stian Brattland <st...@brattland.no>
Subject Re: Streaming Results
Date Thu, 14 Jan 2010 11:56:22 GMT
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 
> <Bernt.Johnsen@sun.com <mailto:Bernt.Johnsen@sun.com>> 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-----
>
>

Mime
View raw message