0

I created a Java Servlet that get query result from mySQL database and print it in XML format. the problem is that it takes a very long time, something about three minutes to print the xml result while in my PHP script it takes 5 seconds.

My Servlet relevant function is: ( run a query and return the xml in a String variable, then, print it to the page )

public String QueryResult(String query)
{
    String retStr;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection ("jdbc:mysql://"+this.host+":"+this.port+"/"+this.db, this.user, this.pass);
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(query);
        ResultSetMetaData rsMetaData = rset.getMetaData();

        retStr = "<Result>\n";
        while (rset.next())
        {
            retStr += "\t<Item>\n";
            for (int i=1;i<=rsMetaData.getColumnCount();i++)
            {
                retStr += "\t\t<"+rsMetaData.getColumnName(i)+">"+ rset.getString(i) + "</"+rsMetaData.getColumnName(i)+">\n";
            }
            retStr += "\t</Item>\n";
        }
        retStr += "</Result>\n";
        stmt.close();
        conn.close();
    }
    catch(Exception e) 
    {
        return "<Result><Error>"+e.toString()+"</Error></Result>";
    }
    return retStr;
}
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Elad
  • 479
  • 3
  • 11
  • 21
  • Do you know which part of this method is taking the time? Is it just the call to executeQuery() or is it the loop that's constructing the result? Also, is this method being called once or many times? – Mike Tunnicliffe Jul 12 '10 at 10:24

5 Answers5

2

String Concatenation:

First of all, when speed matters, you do not want to concatenate strings the way you do. Each time you concatenate a String you are creating a new one.

Better use StringBuilder with a well planned capacity, as the default one won't probably suit your need judging from the code snippet you show.

Also See:

String, StringBuffer, and StringBuilder

Why to use StringBuffer in Java instead of the string concatenation operator

XML:

XStream is a simple library to serialize objects to XML and back again.

Might not be related to your performance issue but might come in handy: XStream

"Performance" does figure in their features list.

Community
  • 1
  • 1
bakkal
  • 54,350
  • 12
  • 131
  • 107
1

You should try to use a StringBuilder to concatenate the XML data. This avoids continously copying of data collected so far. A minor improvement could also be to set the initial capacity (in StringBuilders constructor) to the expected size.

stacker
  • 68,052
  • 28
  • 140
  • 210
1

Strings in Java are immutable, this means that your code creates and destroys a lot of string objects. An obvious optimisation would be to use a StringBuilder or StringBuffer to build your result.

I would not expect this to result in minutes difference between this and your other implementation, so something else might be the problem (a missing table index perhaps?) If you can add logging to your code, so that you get an idea where all this time is spent, we could give a more specific advice.

rsp
  • 23,135
  • 6
  • 55
  • 69
1

Creating/opening/closing connection takes lots time. It is not good for servlet to create own connection - better use connection pooling.

burnall
  • 832
  • 5
  • 10
  • I'm newbie in Java, can you reference me to connection pooling information? – Elad Jul 12 '10 at 10:49
  • Please read this: http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html and http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html – burnall Jul 13 '10 at 09:15
0

If there is a lot of data to concatenate, maybe you would use StringBuffer instead of String.

sinuhepop
  • 20,010
  • 17
  • 72
  • 107