From derby-user-return-10171-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Nov 25 14:00:39 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 60242 invoked from network); 25 Nov 2008 14:00:39 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 25 Nov 2008 14:00:39 -0000 Received: (qmail 77991 invoked by uid 500); 25 Nov 2008 14:00:49 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 77532 invoked by uid 500); 25 Nov 2008 14:00:48 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 77521 invoked by uid 99); 25 Nov 2008 14:00:48 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 25 Nov 2008 06:00:48 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [194.1.211.159] (HELO smtp-gw.artificial-solutions.com) (194.1.211.159) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 25 Nov 2008 13:59:19 +0000 Received: from webmail.artificial-solutions.com (unknown [192.168.106.11]) by smtp-gw.artificial-solutions.com (Postfix) with ESMTP id E59C83E93 for ; Tue, 25 Nov 2008 14:59:32 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 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 14:59:28 +0100 Message-ID: <21F0E9E7ED2D894F8385A92C80336BF3034C0125@arti-sr-28.artificial-solutions.com> In-Reply-To: <492BF70B.1050503@Sun.COM> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: how to get data out from Clob? Thread-Index: AclO/cMObkkZ9583RkWY4HqysefMNQACA94w References: <21F0E9E7ED2D894F8385A92C80336BF3034C0099@arti-sr-28.artificial-solutions.com> <492BF026.6070204@Sun.COM> <21F0E9E7ED2D894F8385A92C80336BF3034C00C9@arti-sr-28.artificial-solutions.com> <492BF70B.1050503@Sun.COM> From: "Mikael Sundberg" To: "Derby Discussion" X-Virus-Checked: Checked by ClamAV on apache.org Thanks for the info=20 Does the same problem exist for Blob? Will update my testservers and try it.=20 -----Original Message----- From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]=20 Sent: den 25 november 2008 14:01 To: Derby Discussion Subject: Re: how to get data out from Clob? Mikael Sundberg wrote: > 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? On the client driver, it doesn't matter (with respect to the bug I'm=20 talking about). I'm afraid you're stuck with the performance problem=20 until you upgrade to a newer version. > We don't want to use any unstable > version on production so guess I will have to solve it some other way. > Il atleast try out the latest version on my testservers to see if that > realy is the problem. > =20 I would consider using the newest bits from the 10.4 branch. The changes after the latest release are bug fixes, not new features. You can download test binaries from here=20 http://dbtg.thresher.com/derby/bits/ Note that these are bits used for the nightly testing and should *not*=20 be used in production without verification and testing. Note that the performance problem in this case is severe; the larger the Clob the worse the impact (we're talking hours instead of=20 seconds/minutes). I would strongly suggest you test with a newer, though unreleased, version. --=20 Kristian > =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: > =20 >> We are having some trouble with retrieving data from big Clob fields. >> The data is about 40-50MB and it takes forever to retrieve it. =20 >> >> Tried a lot of different methods, getString, getClob, getAsciiStream. >> 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 >> >> =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 > 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 > 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