very very low performance while exporting large amount of data to excel

[sridhar gandikota]

Hi Rancher,

I have problem in exporting the large amount of data to excel. There are around 2000 rows or more. When I use resultset iterate through each row is taking lot of time. Can you please help in solving problem.

Is there any way such that PL/SQL procedure will print the html table tags combined with DB values comes out as string output. Then exporting that string to response printwriter? . Is it correct way?

OR is there any other way in java itself to handle this condition.

Please help me Ranchers.

With Regards,
Sridhar

[Deepak Bala]

We have a performance forum. This post probably belongs there

Are you having performance problems with the database query or with exporting the data to excel ? 2000 rows is a relatively small value

[sridhar gandikota]

Not with the sql . For example there are 2000 rows in a table and i need to display on the page when iterate


Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");
String tableString="<table>";
while(rs.next()){
tableString+="<tr>"+"<td>"+rs.getString(1)+"</td>"+"<td>"+rs.getString(2)+"</td>"+"</tr>";
}
tableString+="</table>";

httpServletResponse.setContentType("application/vnd.ms-excel");
httpServletResponse.setHeader("Content-disposition",
"attachment;filename=\"example.xls\"");
httpServletResponse.getWriter().write(tableString);

This is taking lot of time.

Please correct anything wrong in this piece of code.

Or is there any other simple way

[Deepak Bala]

XLS is a custom format and a separate API like POI is needed to allow java to communicate in this format.

A simpler solution is to extract the contents in CSV format. Also there are many things that are unsavory about your code.

No try catch
JDBC , controller , view all in the same place
String concatenation inefficiency
Connection not closed
Writer not flushed

I could go on