aspose file tools*
The moose likes Performance and the fly likes very very low performance while exporting large amount of data to excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "very very low performance while exporting large amount of data to excel" Watch "very very low performance while exporting large amount of data to excel" New topic
Author

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

sridhar gandikota
Ranch Hand

Joined: Jul 09, 2008
Posts: 31
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
Bartender

Joined: Feb 24, 2006
Posts: 6662
    
    5

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


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
sridhar gandikota
Ranch Hand

Joined: Jul 09, 2008
Posts: 31
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
Bartender

Joined: Feb 24, 2006
Posts: 6662
    
    5

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: very very low performance while exporting large amount of data to excel