Formatting resultset for html

[davwhite]

Hi, I have been working on an application which produces html pages and excel spreadsheets from queries from a relational database. I am new to Java and am not aware of all the tools and tricks to optimize the code, so I have constructed a very simple formatter to wrap the results of the resultset in HTML tags. The code works but gets very slow when lots of columns are selected. Here are the basics:

1. The data stored in the database uses 4 tables. Regions, Countries, Clients, and Indicators. Indicators holds all the detail data for each client by quarter.
2. The requirement for the reports is that each row contain individual client information and a column for each quarter for the same client.
Ex.
|Country1|Sector1|Client1Name|Client1Info|Client1Q 1Y1data|Client1Q2Y1data|Client1Q3Y1data|Client1Q4Y 1data
|Country1|Sector1|Client2Name|Client2Info|Client2Q 1Y1data|Client2Q2Y1data|Client2Q3Y1data|Client2Q4Y 1data
|Country1|Sector1|TOTALS| Q1Y1TOTAL | Q2Y1TOTALS | Q3Y1TOTALS | Q4Y1TOTALS
|Country1|Sector2|Client3Name|Client3Info|Client3Q 1Y1data|Client3Q2Y1data|Client3Q3Y1data|Client3Q4Y 1data
|Country1|Sector2|TOTALS| Q1Y1TOTAL | Q2Y1TOTALS | Q3Y1TOTALS | Q4Y1TOTALS
|Country1|TOTALS| Q1Y1TOTAL | Q2Y1TOTALS | Q3Y1TOTALS | Q4Y1TOTALS

3. The user has the option of turning columns on or off for the report.

Using MySQL temporary tables and dynamic query generation from my java code I have managed to create the pivot table which looks exactly like the output I want. The problem is that when all quarters (4 quarters per year for three years) are selected with all indicators (9 indicators per client) for all clients (334 for now) it takes more than 10 minutes to return the page. I have determined that the creation of the temp tables and joins takes less than 5 seconds. The part that takes the longest time is formatting the recordset output with HTML tags.

So my question is this. Are there libraries which can quickly do the formatting for a resultset? Or is there something really stupid I'm doing in my code which is slowing things down? Below is the code and information on my environment.

JDK: 1.6
Server: Apache Tomcat 6.0
Database: MySQL 5.1.18
Platform: Windows (development) Linux (production)

====================Formatting Code ======================

s_Query6 = "select * from temp_pivot5 order by country,sector,client_name,rowtype";
rResults = stmt.executeQuery(s_Query6);
while (rResults.next()) {

boolean BoldTag = false;
String ThisRowType = rResults.getString("rowtype");
if (ThisRowType.equals("0")) {
ResultTable += RowStart;
}
if (ThisRowType.equals("1")) {
ResultTable += SectorTotalRowStart;
BoldTag = true;
}
if (ThisRowType.equals("2")) {
ResultTable += CountryTotalRowStart;
BoldTag = true;
}
for (int a = 0; a < ColCount; a++) {
String ThisIndex = (String) ActiveIndexes.elementAt(a);
if (ThisIndex.equals("1")) {

String ThisRow = " ";
if (rResults.getString(a + 1) != null) {
if (ColTypes.elementAt(a).equals("num")) {
ThisRow = (String) notmoney.format(rResults.getInt(a + 1));
}
if (ColTypes.elementAt(a).equals("perc")) {
ThisRow = (String) notmoney.format(rResults.getInt(a + 1));
ThisRow += "%";
}
if (ColTypes.elementAt(a).equals("text")) {
ThisRow = rResults.getString(a + 1);
}
}
if (BoldTag) {
ResultTable += ColumnStart + BoldTagStart + ThisRow + BoldTagEnd + ColumnEnd;
} else {
ResultTable += ColumnStart + ThisRow + ColumnEnd;
}
}
}

ResultTable += RowEnd;
}