Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
sridhar gandikota
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic