What are likely causes of StringBuilder and ResultSet performance issues

Question

I'm looping through a ResultSet in Java; which for testing purposes is returning about 30 rows with 17 Columns (all String data) per row. I'm manually building an XML String out of the results using StringBuilder and its literally taking about 36 seconds for the loop to finish these iterations.

Note: I realize this isn't the best way to go about getting XML out of a Database, or even the best way to get XML out of a ResultSet - but this has me curious of the slow performance regardless.

Update: As per responses so far I have to address the following: The time to run the Query is less than a second, and I did a System.currentTimeMillis() before and after every section of my code to narrow this down. The 36 seconds is entirely within the code below.

ResultSetMetaData rsmeta = rset.getMetaData();
StringBuilder resultBuilder = new StringBuilder();
resultBuilder.append("<?xml version=\"1.0\" ?><ROWSET>");
if(numColumns != 0){   
   while (rset.next()) {
      resultBuilder.append("<ROW>");
      for (int i = 0; i <= numColumns -1;i++) {
         columnName = rsmeta.getColumnName(i+1);
         resultBuilder.append("<");
         resultBuilder.append(columnName);
         resultBuilder.append(">");
         resultBuilder.append(rset.getString(i+1));
         resultBuilder.append("</");
         resultBuilder.append(columnName);
         resultBuilder.append(">");
      }
      resultBuilder.append("</ROW>");
      numRows += 1;
   }
}
else {
   stmt.close();
   wsConn.close();
   return "No Results";
}

Update: Given the suggestions I received - this code takes roughly the same amount of time give or take half a second.

StringBuilder resultBuilder = new StringBuilder();
resultBuilder.append("<?xml version=\"1.0\" ?><ROWSET>");
if(numColumns != 0){   
   while (rset.next()) {
      resultBuilder.append("<ROW>");
      for (int i = 0; i <= numColumns -1;i++) {
         //columnName = rsmeta.getColumnName(i+1);
         resultBuilder.append("<");
         resultBuilder.append("TestColumnName");
         resultBuilder.append(">");
         //resultBuilder.append(rset.getString(i+1));
         resultBuilder.append("TestData");
         resultBuilder.append("</");
         resultBuilder.append("TestColumnName");
         resultBuilder.append(">");
      }
      resultBuilder.append("</ROW>");
      numRows += 1;
   }
}
else {
   stmt.close();
   wsConn.close();
   return "No Results";
}  

The last test I did having eliminated everything else was to replace the while test with a realistic number of iterations (160, the max rows returned from the small tests I've done previously). The question now is, what could it be about this result set that causes such a slow down.

while (numRows <= 160) {
// same as above
}

Update: As suggested I'll be closing this question as the title doesn't reflect the direction the problem took.

Answer 1

I think your note2 speak by itself.

The time is not lost in the StringBuilder, but somewhere else...

---

columnName = rsmeta.getColumnName(i+1);

Reading the meta-data could be very slow, depending on the implementation. You could read them only once for all resultSets, and reuse them in the loop.

---

UPDATE

From your last update, the StringBuilder is out of concern, and the problem is with the ResultSet. I feel that the question title, and all answers given, are not in synch with your current preoccupation.
I suggest closing this question, and opening a new one for the new preoccupation :-)

Answer 2

I strongly doubt that StringBuilder is your bottleneck, in comparison to the time taken to access and retrieve data from the database. The fact that optimizing simple String concatenation did not significantly change run time confirms this.

You need to look to optimizing how you access the database -- faster connection to the DB, compressed connection, etc.

However I can offer one micro-optimization to your code: instead of appending single-character strings like "<", append a character, like '<'. This however should not make much of any difference.

Answer 3

I agree that the StringBuilder may not be the real time sink here. However, the StringBuffer may be slightly constrained because it ends up having to allocate additional space as the buffer grows (and exceeds it's default size). This suggestion addresses your question, but again, I doubt they'll address your problem.

Good Luck

Answer 4

Try replacing various calls with canned data. For example, someone has suggested that accessing the metadata is the culprit. Try replacing:

columnName = rsmeta.getColumnName(i+1);

with:

columnName = "Column" + i;

where i is an int you set to 0 before the loop and increment through the loop.

Answer 5

Actually reading info from a result set with getColumnName(), next() and getValue() often takes much more time than fetching the result in the first place. This is especially true for non-scrollable result sets.

StringBuilder allocates memory exponentially (newSize = FACTOR*oldSize) so the more you do, the less it needs to reallocate.

To really test this, simply replace rset and rsmeta with a dummy object that has the same methods: let next() return true for a realistic number of rows and have the other methods return realistic-length strings.

Answer 6

I very much doubt that StringBuilder is the culprit here. Java uses it extensively, I have used it extensively, I have rewritten it for my own sort-of-JVM, and basically it is always able to eat characters by the hundreds of millions per second.

I think your trouble comes from the database access itself. When you run a query and get a ResultSet, it does not necessarily mean that all the data has been obtained and internally transformed into an easy-to-manage memory representation. Depending on the database implementation (and its JDBC driver), the ResultSet may be a promise of a number of results, which are dynamically fetched when the ResultSet.next() and ResultSet.getString() methods are called.

Try simply exploring the results, calling all your next() and getString(), but without storing the obtained data in your StringBuilder. If it still takes 36 seconds then StringBuilder is innocent (which I strongly believe it is).

Answer 7

The problem is that the ResultSet has a persistent link to the database and gets more info when its called. May I suggest that you look into a CachedRowSet (javadoc here). It will pull down all the data immediately and acts exactly like a ResultSet otherwise. You can then close your database connection and then start parsing your data. I would suggest trying that and see if it speeds up your process.

Answer 8

Check how you're getting the result set back. There are methods in statement which allow you to change how the ResultSet pulls in data.

In particular, look at

• Statement#setFetchSize
• Statement#setFetchDirection

Answer 9

I had similar problem yesterday: Querying a Microsoft SQL server 10 for about 1.7 million records and reading the values took about 50 minutes. After change; about 80 seconds...

My problem was the jdbc driver used: The only change was going from com.microsoft.sqlserver.jdbc version 1.2 driver to net.sourceforge.jtds version 1.2.2 driver...