You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Glenn Orbon (JIRA)" <de...@db.apache.org> on 2005/05/19 20:20:53 UTC
[jira] Created: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Takes over 3 minutes to insert a 500kb String into CLOB
-------------------------------------------------------
Key: DERBY-302
URL: http://issues.apache.org/jira/browse/DERBY-302
Project: Derby
Type: Bug
Components: SQL
Environment: Windows XP Professional, Dell Pentium IV
Reporter: Glenn Orbon
I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
Here the snippet:
FileReader fr = new FileReader ("sample.txt");
BufferedReader br = new BufferedReader(fr);
String record = null;
try {
while ( (record=br.readLine()) != null ) {
bufferStr.append( record );
}
} catch (IOException e) {
//
// put your error-handling code here
//
System.out.println("Error reading file");
}
System.out.println("Size of inputStr: "+bufferStr.length() );
PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
ps.setInt(1, 13 );
StringReader reader1 = new StringReader(bufferStr.toString());
ps.setCharacterStream(2, reader1, bufferStr.length());
System.out.println("Uploading string....");
ps.execute();
System.out.println("Done uploading string...");
mm.connection.commit();
Thanks for your the help,
Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=all ]
Sunitha Kambhampati updated DERBY-302:
--------------------------------------
Attachment: fixclobperf_0609.txt
Currently in derby, for an insert on a clob using setCharacterStream what will happen is , the entire stream will be materialized into a char array and sent to store for the insert. ( we should not have to stream here. I will file another jira issue for this and put in all information I learnt)
Given this is how inserts for large clobs are happening, the performance issue analysis is as follows:
-- profiler run shows that most time is spent in SQLChar.readExternal which is where the materialization into a char array for the user's input stream happens. The growth of this array happens gradually till the entire stream is materialized into the array. Below code snippet shows by how much the array is grown each time when it realizes it has to read more bytes from the stream.
The dvd hierarchy for clob is - SQLClob ( dvd) extends SQLVarChar extends SQLChar.
So in SQLChar.readExternal
........
int growby = in.available();
if(growby < 64)
growby = 64
and then an allocation and an arraycopy to the new allocated array.
-- In the code snippet, 'in' is the wrapper around the user's stream which is ReaderToUTF8Stream . ReaderToUTF8Stream extends InputStream and does not override available() method . As per the spec, InputStream.available() returns 0.
-- Thus each time, the array growth is by 64 bytes which is obviously not performant. so for a 500k clob insert, this would mean allocation & arraycopy steps happen ~8000 times.
-- The ReaderToUTF8Stream that has the user's stream reads from the stream and does the utf8 conversion and puts it in a 4k array. I think it is reasonable to have a 32k buffer to store this information for clobs.
Although I think there seems to be more possible optimizations in this area, I prefer the incremental approach too :) so this patch is a first step towards fixing the insert clob performance in the current system.
Fix includes:
-- enhanced the way the array was grown to keep the original 64 bytes for char ( seems reasonable given the upper limit for char) but override it to have 4k for varchar and clobs.
-- override available() in ReaderToUTF8Stream to return a better estimate of how many bytes can be read.
svn stat
M java\engine\org\apache\derby\impl\jdbc\ReaderToUTF8Stream.java
M java\engine\org\apache\derby\iapi\services\io\LimitReader.java
M java\engine\org\apache\derby\iapi\types\SQLChar.java
M java\engine\org\apache\derby\iapi\types\SQLVarchar.java
-- ran derbyall ok with sun jvm.
-- I can add a test and compare times but I realize that is probably not the best solution here. It should ideally be part of a performance regression suite.
Numbers for clob inserts in seconds for one insert on my laptop - as per the jira issue.
With fix , times are in seconds for 1 insert on a clob on my laptop (windows, 1G ram, 1.6Ghz Intel Pentium(M) )
FileSize ibm jvm 1.4.2 sun jvm 1.4.2 sun jvm 1.5
500k 0.9s 1.6s 1.7s
1M 2.1s 4s 5s
2M 3s 9s 11s
4M 7s 18s 22s
Without the fix, 500k with sun jvm takes 3 mins and ibm jvm takes 20 seconds.
I will add the test program along with the input files to jira issue.
_________________________
Without this fix : As I already mentioned in the jira comment for derby302, I changed the program in the attached jira entry to use BufferedReader with the buffersize set to a bigger value than the default ( to 64k) brought down the times for sun jvm closer to ibm jvm. I noticed that in my test, if I ran the test multiple times and did multiple inserts the performance of sun jvm and ibm jvm for 500k clob was around 20 seconds - guess the jit kicks in , plus the OS cache may also be a factor..
________________________
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
> Assignee: Sunitha Kambhampati
> Attachments: fixclobperf_0609.txt
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=comments#action_12313346 ]
Sunitha Kambhampati commented on DERBY-302:
-------------------------------------------
I just realized since the link I provided in my earlier comment to mike's email goes to 2 lines .. the link doesnt open up, so attaching the shorter gmane link to his mail and thread on derby dev.
http://article.gmane.org/gmane.comp.apache.db.derby.devel/5405
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
> Assignee: Sunitha Kambhampati
> Attachments: fixclobperf_0609.txt, fixclobperf_0610.txt
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=all ]
Sunitha Kambhampati reassigned DERBY-302:
-----------------------------------------
Assign To: Sunitha Kambhampati
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
> Assignee: Sunitha Kambhampati
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=all ]
Sunitha Kambhampati updated DERBY-302:
--------------------------------------
Attachment: fixclobperf_0610.txt
This patch addresses Mike's comments (#1 and #2)in http://mail-archives.apache.org/mod_mbox/db-derby-dev/200506.mbox/%3c42A9CE92.7070808@sbcglobal.net%3e
1) Added final to the methods except for SQLChar for performance reasons.
2) Added constants to improve readability for all the humbers.
- Ran derbyall on jdk142 OK.
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
> Assignee: Sunitha Kambhampati
> Attachments: fixclobperf_0609.txt, fixclobperf_0610.txt
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=comments#action_12312888 ]
Sunitha Kambhampati commented on DERBY-302:
-------------------------------------------
For Sun JVM ( 1.4.2 _07) the first insert takes 3 minutes but with IBM jvm 1.4.2 it takes about 20seconds on my laptop. Further with Sun JVM you can improve performance if you use the BufferedReader and give a large buffer size to it.
Adding this as a comment here so the info is not lost. I have just started looking into this issue, but if someone else has other suggestions etc, please post here/to the list.
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Glenn Orbon (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=comments#action_65858 ]
Glenn Orbon commented on DERBY-302:
-----------------------------------
I am running Cloudscape as an embedded database.
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-302) Takes over 3 minutes to insert
a 500kb String into CLOB
Posted by Sunitha Kambhampati <ks...@gmail.com>.
Kathey Marsden wrote:
>Sunitha Kambhampati (JIRA) wrote:
>
>
>
>>using setString instead of setCharacterStream in this case ( insert of 500kb clob) performs way much better. But ofcourse, setString cannot be used for large data (or data that wont fit into memory) and one would need to use streams.
>>
>>
>>
>>
>>
>Is there a similar performance impact with getCharacterStream vs
>getString() or is it generally ok to use getCharacterStream()?
>
>Thanks
>
>Kathey
>
>
>
Hi Kathey,
I have not done a lot of analysis with getCharacterStream versus
getString so dont have much data to give you. . But for some simple
cases that I tried ( 500k) the difference in using getCharacterStream
versus getString was not so drastic as the case of the insert( ie
setString, setCharacterStream).
In general, you would use getCharacterStream when you are retrieving
large amounts of data otherwise getString is probably good.
Sunitha.
Re: [jira] Commented: (DERBY-302) Takes over 3 minutes to insert
a 500kb String into CLOB
Posted by Kathey Marsden <km...@sbcglobal.net>.
Sunitha Kambhampati (JIRA) wrote:
>using setString instead of setCharacterStream in this case ( insert of 500kb clob) performs way much better. But ofcourse, setString cannot be used for large data (or data that wont fit into memory) and one would need to use streams.
>
>
>
Is there a similar performance impact with getCharacterStream vs
getString() or is it generally ok to use getCharacterStream()?
Thanks
Kathey
[jira] Commented: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=comments#action_65878 ]
Sunitha Kambhampati commented on DERBY-302:
-------------------------------------------
using setString instead of setCharacterStream in this case ( insert of 500kb clob) performs way much better. But ofcourse, setString cannot be used for large data (or data that wont fit into memory) and one would need to use streams.
Inserting a 500kb blob using setBinaryStream is also much faster than the setCharacterStream.
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-302) Takes over 3 minutes to insert a 500kb String into CLOB
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-302?page=all ]
Sunitha Kambhampati closed DERBY-302:
-------------------------------------
Fix Version: 10.1.1.0
Resolution: Fixed
has been committed with svn 190415.
> Takes over 3 minutes to insert a 500kb String into CLOB
> -------------------------------------------------------
>
> Key: DERBY-302
> URL: http://issues.apache.org/jira/browse/DERBY-302
> Project: Derby
> Type: Bug
> Components: SQL
> Environment: Windows XP Professional, Dell Pentium IV
> Reporter: Glenn Orbon
> Assignee: Sunitha Kambhampati
> Fix For: 10.1.1.0
> Attachments: fixclobperf_0609.txt, fixclobperf_0610.txt
>
> I downloaded a Cloudescape Version 10 from here http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html. Not sure exactly which version of 10 this is.
> Would like to store up to a 10MB XML string in a CLOB. However, I noticed it took over 3 minutes to insert a 500kb string. I am using the PreparedStatement to get around the 37kb limitation encountered in a previous issue. It takes less than 2 seconds to insert a 500kb string into a CLOB in mySQl.
> Here the snippet:
> FileReader fr = new FileReader ("sample.txt");
> BufferedReader br = new BufferedReader(fr);
> String record = null;
> try {
> while ( (record=br.readLine()) != null ) {
> bufferStr.append( record );
> }
> } catch (IOException e) {
> //
> // put your error-handling code here
> //
> System.out.println("Error reading file");
> }
> System.out.println("Size of inputStr: "+bufferStr.length() );
>
> PreparedStatement ps = mm.connection.prepareStatement("INSERT into documents VALUES (?,?)" );
> ps.setInt(1, 13 );
> StringReader reader1 = new StringReader(bufferStr.toString());
> ps.setCharacterStream(2, reader1, bufferStr.length());
> System.out.println("Uploading string....");
> ps.execute();
> System.out.println("Done uploading string...");
> mm.connection.commit();
> Thanks for your the help,
> Glenn O.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira