Preface:
I have some data being inserting into a mySQL table every 5 minutes. Currently, there are about 275 rows. Each row has 26 (TEXT) columns, the data in those columns is no longer than 10-15 characters.
I am using the following code to turn this data into XML which is then, through a servlet, output on a webserver. With 275 rows, this is taking around 3-4 minutes to generate the XML file. I am worried that in a week from now when the data is over 2,000 rows it will exponentially decrease in speed. Can someone give me a better route so this will run faster?
-
String xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n\n";
-
String SQLCommand = "select * from "+getDataId()+";";
-
try{
-
Statement stmt;
-
ResultSet rs;
-
String url = "jdbc:mysql://"+getIBoxIp()+":"+getDbPort()+"/"+getDbName();
-
Connection con = DriverManager.getConnection(url,getDbUsername(), getDbPassword());
-
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
-
rs = stmt.executeQuery(SQLCommand);
-
while(rs.next()){
-
xml += "\n<row>";
-
ResultSetMetaData md = rs.getMetaData();
-
for(int i = 1; i < md.getColumnCount() + 1; i++){
-
xml += "\n\t<"+md.getColumnName(i)+">"+rs.getString(i)+"</"+md.getColumnName(i)+">";
-
}
-
xml += "\n</row>\n";
-
}
-
System.out.println("@@mysql> XMLSQLCommand > " + SQLCommand);
-
con.close();
-
return xml;
-
}catch(Exception ex)
-
{
-
System.out.println("@@mysql> SQLCommand Failed > " + SQLCommand + "\n@@mysql> Exception > " + ex.getMessage() + "");
-
return xml;
-
}
Again, with 275 rows, I'm getting about 250 KB of data in the resulting XML file with a 3-5 minute parsing/generation time.
Thanks in advance!!!
