Performance issues with large ResultSet

[james]

Hi all,

I'm experiencing some performance issues when processing ResultSet's
containing large amounts of data. The data is typically around 2,000
rows and although the database query only takes 0.9 seconds,
processing the results takes almost 30 seconds. All i'm doing is
iterating through the resultSet and placing the values into an xml
string. The code that does this is shown below.

while(rs.next()) {
// build XML
xmlResult += "<student studentID='" +
rs.getInt("StudentID") + "' firstName='" + rs.getString("FirstName") +
"' lastName='" + rs.getString("LastName") + "' gender='" +
rs.getString("Gender") +"' />";
} // End of loop

xmlResult is of type String, could this be causing performance issues?

I'm pretty new to java so this could just be a simple problem however
googling hasn't brought up much.

TIA
James

 

[Jean-Baptiste Nizet]

Hi all,

I'm experiencing some performance issues when processing ResultSet's
containing large amounts of data. The data is typically around 2,000
rows and although the database query only takes 0.9 seconds,
processing the results takes almost 30 seconds. All i'm doing is
iterating through the resultSet and placing the values into an xml
string. The code that does this is shown below.

while(rs.next()) {
// build XML
xmlResult += "<student studentID='" +
rs.getInt("StudentID") + "' firstName='" + rs.getString("FirstName") +
"' lastName='" + rs.getString("LastName") + "' gender='" +
rs.getString("Gender") +"' />";

} // End of loop

xmlResult is of type String, could this be causing performance issues?

No. But the way you're building it is the problem. Read
http://docs.sun.com/app/docs/doc/819-3681/abebh?a=view and you'll
understand why your code is slow: it creates lots of big String
instances only to discard them at the end of each iteration. Use a
StringBuilder instead:

StringBuilder builder = new StringBuilder();
while(rs.next()) {
// build XML
builder.append("<student studentID='");
builder.append(rs.getInt("StudentID"));
builder.append("' firstName='");
builder.append(rs.getString("FirstName"));
builder.append("' lastName='");
builder.append(rs.getString("LastName"));
builder.append("' gender='");
builder.append(rs.getString("Gender"));
builder.append("' />");
}
String xmlResult = builder.toString();

As a side note, I hope that none of your student is named O'Reilly,
because it would cause your generated XML to be invalid. Think about
escaping the XML values (see http://commons.apache.org/lang/api/org/apache/commons/lang/StringEscapeUtils.html).
As another side note, if your result set is really large, storing it
completely as huge XML String in memory might not be a good idea. You
might have to write it to disk instead.

JB.

 

[james]

No. But the way you're building it is the problem. Readhttp://docs.sun.com/app/docs/doc/819-3681/abebh?a=viewand you'll
understand why your code is slow: it creates lots of big String
instances only to discard them at the end of each iteration. Use a
StringBuilder instead:

StringBuilder builder = new StringBuilder();
while(rs.next()) {
  // build XML
  builder.append("<student studentID='");
  builder.append(rs.getInt("StudentID"));
  builder.append("' firstName='");
  builder.append(rs.getString("FirstName"));
  builder.append("' lastName='");
  builder.append(rs.getString("LastName"));
  builder.append("' gender='");
  builder.append(rs.getString("Gender"));
  builder.append("' />");}

String xmlResult = builder.toString();

Using a StringBuilder has taken off a huge amount of time and the
whole operation now takes around 2-5 seconds. Thanks

As a side note, I hope that none of your student is named O'Reilly,
because it would cause your generated XML to be invalid. Think about
escaping the XML values (seehttp://commons.apache.org/lang/api/org/apache/commons/lang/StringEsca...).

The XML is escaped I just missed that bit out of the example code for
simplicities sake.

As another side note, if your result set is really large, storing it
completely as huge XML String in memory might not be a good idea. You
might have to write it to disk instead.

I'm going to do some test's on this to see if it improves performance.

Thanks for all your help!