db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: Streaming Results
Date Thu, 14 Jan 2010 11:17:35 GMT
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>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)
> >>
> >> 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