Performance issues with large ResultSet

Discussion in 'Java' started by james, Jul 31, 2008.

  1. james

    james Guest

    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
     
    james, Jul 31, 2008
    #1
    1. Advertisements

  2. 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.
     
    Jean-Baptiste Nizet, Jul 31, 2008
    #2
    1. Advertisements

  3. james

    james Guest

    Using a StringBuilder has taken off a huge amount of time and the
    whole operation now takes around 2-5 seconds. Thanks
    The XML is escaped I just missed that bit out of the example code for
    simplicities sake.
    I'm going to do some test's on this to see if it improves performance.

    Thanks for all your help!
     
    james, Jul 31, 2008
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.