Re: Performance issues with large ResultSet
On 31 juil, 11:59, james <james.herring...@tiscali.co.uk> wrote:
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.