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. Advertising

  2. On 31 juil, 11:59, james <> 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.
     
    Jean-Baptiste Nizet, Jul 31, 2008
    #2
    1. Advertising

  3. james

    james Guest


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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matrix
    Replies:
    0
    Views:
    596
    Matrix
    Apr 1, 2004
  2. Replies:
    0
    Views:
    747
  3. Tim B
    Replies:
    4
    Views:
    624
  4. Tincy
    Replies:
    0
    Views:
    1,916
    Tincy
    Jan 13, 2009
  5. sa 125
    Replies:
    15
    Views:
    503
    James Gray
    May 14, 2009
Loading...

Share This Page