I am calling a JSP program to extract data from a database to excel. The database table has 112 columns and 35k records. Its taking an hour to extract the data. Any suggestions on how to make it under 5 mins? I have seen many similar posts but could not find any that could improve the performance. Appreciate your suggestions. My working code below -
<%@page import="java.io.FileInputStream"%>
<%@page import="java.io.FileOutputStream"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.ResultSetMetaData"%>
<%@page import="java.util.*"%>
<%@page import="java.sql.SQLException"%>
<%@page import="org.apache.poi.ss.usermodel.Cell"%>
<%@page import="org.apache.poi.ss.usermodel.Row"%>
<%@page import="org.apache.poi.ss.usermodel.Sheet"%>
<%@page import="org.apache.poi.ss.usermodel.Workbook"%>
<%@page import="org.apache.poi.ss.usermodel.WorkbookFactory"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Export Data to Excel File</title>
</head>
<body>
<%
HSSFWorkbook writeWorkbook = new HSSFWorkbook();
HSSFSheet desSheet = writeWorkbook.createSheet("sheet");
Connection connection = null;
ResultSet rs = null;
int counter=1;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@PRD:PRD","ABC","ABC");
Statement statement = connection.createStatement();
String sql = ("select * from tblnm");
rs = statement.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
Row desRow1 = desSheet.createRow((short)0);
for(int col=0 ;col < columnsNumber;col++) {
Cell newpath = desRow1.createCell(col);
newpath.setCellValue(rsmd.getColumnLabel(col+1));
}
while(rs.next()) {
System.out.println("Row number" + rs.getRow() );
Row desRow = desSheet.createRow(rs.getRow());
for(int col=0 ;col < columnsNumber;col++) {
Cell newpath = desRow.createCell(col);
newpath.setCellValue(rs.getString(col+1));
}
FileOutputStream fileOut = new FileOutputStream("C:/test.xls");
writeWorkbook.write(fileOut);
fileOut.flush();
fileOut.close();
out.println("Your excel file has been generated!");
writeWorkbook.write(response.getOutputStream());
}
}
catch (SQLException e) {
System.out.println("Failed to get data from database");
}
%>
</body>
</html> 
I modified the code to pull the data dynamically instead of file out and byte array method.