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)
Date: Tue, 25 Nov 2008 13:31:34 +0100
From: Kristian Waagan <Kristian.Waagan@Sun.COM>
Subject: Re: how to get data out from Clob?
In-reply-to: 
 <21F0E9E7ED2D894F8385A92C80336BF3034C0099@arti-sr-28.artificial-solutions.com>
Sender: Kristian.Waagan@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <492BF026.6070204@Sun.COM>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: 
 <21F0E9E7ED2D894F8385A92C80336BF3034C0099@arti-sr-28.artificial-solutions.com>
User-Agent: Thunderbird 2.0.0.17 (X11/20081023)

Mikael Sundberg write:
>
> 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.  
>
> 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?
>
>  
>


Hello Mikael,

You are being hit by a serious performance bug (a lot more prominent 
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 
the 10.4 branch, but a release hasn't been made after the fix was 
committed. If you don't want to build Derby yourself, *test binaries* 
are available if you want to test with a newer version. It should be 
sufficient to update the server side only (i.e. derbynet.jar and 
derby.jar). If you try it out, please remember to take a backup of your 
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 
older releases. You can adjust the buffer/block size used to fetch data 
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 
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 
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 
feature release.

We're also in the progress of adding performance regression tests for 
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 
interested in getting more details from you.


regards,
-- 
Kristian