Results 1 to 6 of 6
  1. #1
    Enthiran is offline Member
    Join Date
    Dec 2010
    Posts
    10
    Rep Power
    0

    Exclamation How to handle heap space?

    Hi..

    My program stores around 60,000 records in result set from a database and i have to write to a Excel file.While writing the 15000th record heap space memory error occurs. I increased the JVM heap space but it didn't help me much.. Is there a way like i can use flush() or read data in blocks.

    ResultSet res = queryStatement.executeQuery();
    ResultSetMetaData rsmd = res.getMetaData();
    int numCols = rsmd.getColumnCount();
    FileOutputStream fout = new FileOutputStream("Cert_Report.xls");
    HSSFWorkbook certWorkbook = new HSSFWorkbook();
    HSSFSheet certWorksheet = null;
    HSSFRow sheetRow = null;
    HSSFCell sheetCell = null;

    int rowcount = 0;
    int sheetcount = 0;
    while (res.next()){

    if(rowcount%30000==0) // This is to create a new worksheet for every 30000 records
    {
    sheetcount++;
    rowcount =0;
    certWorksheet = certWorkbook.createSheet(appName+"_CertReport"+she etcount);

    }
    sheetRow = certWorksheet.createRow(rowcount);
    for(int i=1;i<=numCols;i++){

    sheetCell = sheetRow.createCell((short)(i-1));

    sheetCell.setCellValue(res.getString(i));
    }
    System.out.println(rowcount);
    rowcount++;


    }

    certWorkbook.write(fout);
    fout.close();


    Please suggest me a solution...

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Don't use Excel?
    The reason you are having this problem is it's a memory hog.
    You don't have the option of streaming an Excel document out, so it's all there in memory.

    Why do you need to have 60,000 rows in a single Excel file?
    No one's going to be able to read that lot.

  3. #3
    Enthiran is offline Member
    Join Date
    Dec 2010
    Posts
    10
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Don't use Excel?
    The reason you are having this problem is it's a memory hog.
    You don't have the option of streaming an Excel document out, so it's all there in memory.

    Why do you need to have 60,000 rows in a single Excel file?
    No one's going to be able to read that lot.
    Is there a way that i can read the data from the database in chunks or else will creating new workbooks workout... but at least i need 30,000 records in a workbook. But it gets stuck with 15000.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    It's not the db causing the problems.
    It's Excel, or POI in your case...though POI doesn't really have an option.

    Because you can't stream the data to a file on disk, you have to have it all in memory.

    So, if you insist on using Excel then you have to reduce the size of your Excel files.

    Why do you need an Excel document that's going to be soo large no one is going to actually use it?

  5. #5
    Enthiran is offline Member
    Join Date
    Dec 2010
    Posts
    10
    Rep Power
    0

    Default

    I use those excel files.. Sick job.. So like can i create workbooks with 10,000 records each....??

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Would CSVs do the job rather than Excel files?

    Anyway, you could use 10000 record workbooks so long as you remember to close off the workbooks so they can be garbage collected.

Similar Threads

  1. Java Heap Space
    By a.r.viswanath@gmail.com in forum Eclipse
    Replies: 2
    Last Post: 07-02-2010, 05:34 AM
  2. no heap space... need more heap
    By anupam.kumar in forum Advanced Java
    Replies: 3
    Last Post: 02-08-2010, 04:42 PM
  3. Heap Space Problem
    By segolas in forum Advanced Java
    Replies: 6
    Last Post: 01-14-2010, 11:29 AM
  4. Java Heap Space
    By sandeeprao.techno in forum Advanced Java
    Replies: 19
    Last Post: 10-30-2008, 11:27 AM
  5. Java heap space?
    By javanewbie in forum New To Java
    Replies: 1
    Last Post: 06-24-2008, 06:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •