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 (athena.apache.org: local policy)
MIME-version: 1.0
Date: Thu, 14 Jan 2010 13:06:02 +0100
From: "Bernt M. Johnsen" <Bernt.Johnsen@Sun.COM>
Subject: Re: Streaming Results
In-reply-to: <71a64ba61001140317td96295bg9d0271c9837b3377@mail.gmail.com>
Sender: Bernt.Johnsen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <20100114120602.GC6588@atum01.Norway.Sun.COM>
Organization: Sun Microsystems
Content-type: multipart/signed; boundary=t0UkRYy7tHLRMCai;
 protocol="application/pgp-signature"; micalg=pgp-sha1
Content-disposition: inline
References: <4B4ECB24.4020607@brattland.no>
 <20100114090801.GA6588@atum01.Norway.Sun.COM> <4B4EEB87.5070301@brattland.no>
 <20100114103335.GB6588@atum01.Norway.Sun.COM>
 <71a64ba61001140317td96295bg9d0271c9837b3377@mail.gmail.com>
User-Agent: Mutt/1.5.17+20080114 (2008-01-14)


--t0UkRYy7tHLRMCai
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hi,

>>>>>>>>>>>> Brett Wooldridge wrote (2010-01-14 20:17:35):
> Bernt,
>=20
> 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.

No. My point is that Derby won't materialize the entire result set on
the client side. The driver will usually materialize the number of
rows that fits into the 32K communications buffer, and when the buffer
is exhausted, it will fetch and materialize another 32K.

> 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 m=
uch
> smaller than a million).
>=20
> 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.

What you call "ResultSet streaming" is as far is I know the normal
behaviour of most JDBC drivers.

>=20
> Brett
>=20
>=20
> On Thu, Jan 14, 2010 at 7:33 PM, Bernt M. Johnsen <Bernt.Johnsen@sun.com>=
wrote:
>=20
> > >>>>>>>>>>>> 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, a=
nd
> > > 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 a=
t a
> > > time from the database
> > > (yes, some buffers are most likely involved). However, my key point w=
as
> > > 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, numb=
er
> > >> 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 =3D 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 o=
ne
> > >> 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=3D
> > =3DD9VP
> > -----END PGP SIGNATURE-----
> >
> >

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

--t0UkRYy7tHLRMCai
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLTwiqlFBD9TXBAPARAhklAJ9ffRM6EWnuNCaoEBDXKKK7NnUpNACggtMa
FEMIusrX5pSF073Qg/6UdCk=
=FmcW
-----END PGP SIGNATURE-----

--t0UkRYy7tHLRMCai--