Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement: Performance bottleneck in using new String(...) in setString meth

 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gurus,
I need to insert 3351 bytes of data in to a datatabase table, which has 199 columns. I need to repeat this sequence 314K times.
The database table column data type is "CHAR".
"java -prof" shows there is performance bottleneck in using "new String(...)" while using setString(...).
Can some body suggest any good alternatives to new String(...)?
Thanks in advance.
Here is Code:-
long count = 0;
int i_flush_frequency = 100;
public void insertRec(byte[] rec)
throws SQLException, ClassNotFoundException, IOException
{
try {
pstmt.setString(1, new String(rec, 0, 1));
pstmt.setString(2, new String(rec, 1, 8));
...................
pstmt.setString(199, new String(rec, 3350, 1));
pstmt.addBatch();
count++;
if (count % i_flush_frequency == 0) {
pstmt.executeBatch();
old_count = count;
}
}
catch (SQLException se) {
System.out.println("Caught Exception" + se.getMessage());
}
}//end insertRec()
public void flushBatch()
throws SQLException, ClassNotFoundException, IOException
{
if ( count != old_count ){
try{
pstmt.executeBatch();
}catch ( SQLException se ){
System.out.println("Caught Exception"+se.getMessage());
}
}//end if
}//end flushBatch()
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How is the byte[] being generated? Were the 199 items distinct variables at some time? If so, try using a Data Transfer Object instead of converting to a byte[].
 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The byte is being generated by another java class(main). Currently all 199 columns are of CHAR type in the database. They have their own different size ( all are not of same size ). I will look in to DataTransfer Object.
Thanks.
 
Bill Pugh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code you have is creating new character arrays and copying characters for each String object.
An alternative is to create one big String object from the entire rec:
String everything = new String(rec);
Then create substrings for each setString. For example:
pstmt.setString(1, everything.substring(0,1));
pstmt.setString(2, everything.substring(1,8));
Under Sun's current implementation, all of the String objects will share the same character
array, and no copying of characters will be required. That will almost certainly be a win.
Of course, future implementations may change, but they almost certainly won't be any worse than what you have now.
 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I didn't see any performance improvement after replacing new String(...) by StringBuffer.substring(...). :-(
Here is the code:-
StringBuffer sb = new StringBuffer();
public void insertRec(byte[] b)
throws SQLException, ClassNotFoundException, IOException
{
sb = sb.delete(0,3350);//clean up StringBuffer.
sb.append( b);
pstmt.setString(1, sb.substring(0, 1));
pstmt.setString(2, sb.substring(1, 9));
................
pstmt.setString(199, sb.substring(3350, 3351));
//no change in rest part of code...
}
[ August 08, 2003: Message edited by: Venkat Nagam ]
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depending upon what database you are using, there's a good chance that setBytes would work as well, but you'd have to chop up the array many times using some method from the java.util.Arrays class.
Maybe the problem could be solved by looking at how the byte[] is created, and why. Couldn't a data transfer object be loaded, or a String at that time?
 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dana. The basis data is in Char Array. Char to byte conversion sucks performance. :-(
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic