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)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: how to get data out from Clob?
Date: Tue, 25 Nov 2008 13:41:25 +0100
Message-ID: 
 <21F0E9E7ED2D894F8385A92C80336BF3034C00C9@arti-sr-28.artificial-solutions.com>
In-Reply-To: <492BF026.6070204@Sun.COM>
Thread-Topic: how to get data out from Clob?
Thread-Index: AclO+ZQCfj7hayTlTa2PdP1i6hvSzQAAMinw
References: 
 <21F0E9E7ED2D894F8385A92C80336BF3034C0099@arti-sr-28.artificial-solutions.com>
 <492BF026.6070204@Sun.COM>
From: "Mikael Sundberg" <Mikael.Sundberg@artificial-solutions.com>
To: "Derby Discussion" <derby-user@db.apache.org>

Hi
Im using derby 10.4.2 and the clientdriver.
I changed from=20
                BufferedReader r =3D new BufferedReader(new
InputStreamReader(res.getAsciiStream("data")));
                for (String line =3D r.readLine(); line !=3D null; line =
=3D
r.readLine())
                {
                    w.println(line);
                }

To
	String data =3D res.getString("data");

In a recent update to our application since the getString seemed to be
twice as fast then. Not entirely sure what versions of derby I tested on
then.
So bacisly I should change back now? We don't want to use any unstable
version on production so guess I will have to solve it some other way.=20
Il atleast try out the latest version on my testservers to see if that
realy is the problem.

=20
-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]=20
Sent: den 25 november 2008 13:32
To: Derby Discussion
Subject: Re: how to get data out from Clob?

Mikael Sundberg write:
>
> We are having some trouble with retrieving data from big Clob fields.=20
> The data is about 40-50MB and it takes forever to retrieve it. =20
>
> Tried a lot of different methods, getString, getClob, getAsciiStream.=20
> They all take forever (hours).
>
> When checking whats running in derby we find
>
> CALL SYSIBM.CLOBGETSUBSTRING(?, ?, ?)
>
> A lot of times.
>
> How should I retrieve large CLob fields fastest?
>
> =20
>


Hello Mikael,

You are being hit by a serious performance bug (a lot more prominent=20
since Derby 10.3).
I understand you are using the client driver, but which version of Derby

are you using?

I believe the issue you are experiencing has been fixed in trunk and in=20
the 10.4 branch, but a release hasn't been made after the fix was=20
committed. If you don't want to build Derby yourself, *test binaries*=20
are available if you want to test with a newer version. It should be=20
sufficient to update the server side only (i.e. derbynet.jar and=20
derby.jar). If you try it out, please remember to take a backup of your=20
database! If you need to upgrade you database to trunk, you also need to

enable pre-release upgrades.

Unfortunately, there is no way to properly work around the problem in=20
older releases. You can adjust the buffer/block size used to fetch data=20
in the client application, but this will only help a little bit and will

probably not be sufficient.
The optimal buffer size is dependent on the Clob content (due to the=20
UTF-8 encoding used). If your Clobs contain ASCII data, you should set=20
your buffer size to 32672 (note that this is smaller than 32KB). If you=20
are using the embedded driver, make sure you access the Clob using one=20
of the streaming methods (getSubString has been fixed by now).
There will be several changes regarding Clob performance in the next=20
feature release.

We're also in the progress of adding performance regression tests for=20
LOBs, as this is a problem we should have detected a long time ago!
If you are still seeing the problem with the newest version, I'm very=20
interested in getting more details from you.


regards,
--=20
Kristian