Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet

Question

I am getting Java Heap Space Error while writing large data from database to an excel sheet. I dont want to use JVM -XMX options to increase memory.

Following are the details:

1) I am using org.apache.poi.hssf api for excel sheet writing.

2) JDK version 1.5

3) Tomcat 6.0

Code i have wriiten works well for around 23 thousand records, but it fails for more than 23K records.

Following is the code:

ArrayList l_objAllTBMList= new ArrayList();
    l_objAllTBMList = (ArrayList) m_objFreqCvrgDAO.fetchAllTBMUsers(p_strUserTerritoryId);
    ArrayList l_objDocList = new ArrayList();
    m_objTotalDocDtlsInDVL= new HashMap();
    Object l_objTBMRecord[] = null;
    Object l_objVstdDocRecord[] = null;
    int l_intDocLstSize=0;
    VisitedDoctorsVO l_objVisitedDoctorsVO=null;
    int l_tbmListSize=l_objAllTBMList.size();
    System.out.println(" getMissedDocDtlsList_NSM ");

        for(int i=0; i<l_tbmListSize;i++)
        {
            l_objTBMRecord = (Object[]) l_objAllTBMList.get(i);

            l_objDocList = (ArrayList) m_objGenerateVisitdDocsReportDAO.fetchAllDocDtlsInDVL_NSM((String) l_objTBMRecord[1], p_divCode, (String) l_objTBMRecord[2], p_startDt, p_endDt, p_planType, p_LMSValue, p_CycleId, p_finYrId);
            l_intDocLstSize=l_objDocList.size();
            try {
                    l_objVOFactoryForDoctors = new VOFactory(l_intDocLstSize, VisitedDoctorsVO.class); 

/* Factory class written to create and maintain limited no of Value Objects (VOs)*/

                } catch (ClassNotFoundException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                } catch (InstantiationException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                } catch (IllegalAccessException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                }


                for(int j=0; j<l_intDocLstSize;j++)
                {
                    l_objVstdDocRecord = (Object[]) l_objDocList.get(j);
                    l_objVisitedDoctorsVO = (VisitedDoctorsVO) l_objVOFactoryForDoctors.getVo();
                    if (((String) l_objVstdDocRecord[6]).equalsIgnoreCase("-"))
                    {
                        if (String.valueOf(l_objVstdDocRecord[2]) != "null")
                        {
                            l_objVisitedDoctorsVO.setPotential_score(String.valueOf(l_objVstdDocRecord[2]));
                            l_objVisitedDoctorsVO.setEmpcode((String) l_objTBMRecord[1]);
                            l_objVisitedDoctorsVO.setEmpname((String) l_objTBMRecord[0]);
                            l_objVisitedDoctorsVO.setDoctorid((String) l_objVstdDocRecord[1]);
                            l_objVisitedDoctorsVO.setDr_name((String) l_objVstdDocRecord[4] + " " + (String) l_objVstdDocRecord[5]);
                            l_objVisitedDoctorsVO.setDoctor_potential((String) l_objVstdDocRecord[3]);
                            l_objVisitedDoctorsVO.setSpeciality((String) l_objVstdDocRecord[7]);
                            l_objVisitedDoctorsVO.setActualpractice((String) l_objVstdDocRecord[8]);

                            l_objVisitedDoctorsVO.setLastmet("-");
                            l_objVisitedDoctorsVO.setPreviousmet("-");
                            m_objTotalDocDtlsInDVL.put((String) l_objVstdDocRecord[1], l_objVisitedDoctorsVO);
                        }

                    }

                }// End of While
               writeExcelSheet(); // Pasting this method at the end

            // Clean up code
            l_objVOFactoryForDoctors.resetFactory(); 
            m_objTotalDocDtlsInDVL.clear();// Clear the used map
            l_objDocList=null;
            l_objTBMRecord=null;
            l_objVstdDocRecord=null;

        }// End of While
        l_objAllTBMList=null;
        m_objTotalDocDtlsInDVL=null;

-------------------------------------------------------------------
private void writeExcelSheet() throws IOException
 {
        HSSFRow l_objRow = null;
        HSSFCell l_objCell = null;
        VisitedDoctorsVO l_objVisitedDoctorsVO = null;
        Iterator l_itrDocMap = m_objTotalDocDtlsInDVL.keySet().iterator();
        while (l_itrDocMap.hasNext())
        {
            Object key = l_itrDocMap.next();
            l_objVisitedDoctorsVO = (VisitedDoctorsVO) m_objTotalDocDtlsInDVL.get(key);
            l_objRow = m_objSheet.createRow(m_iRowCount++);

            l_objCell = l_objRow.createCell(0);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(String.valueOf(l_intSrNo++));

            l_objCell = l_objRow.createCell(1);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getEmpname() + " (" + l_objVisitedDoctorsVO.getEmpcode() + ")"); // TBM Name

            l_objCell = l_objRow.createCell(2);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getDr_name());// Doc Name

            l_objCell = l_objRow.createCell(3);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getPotential_score());// Freq potential score

            l_objCell = l_objRow.createCell(4);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getDoctor_potential());// Freq potential score

            l_objCell = l_objRow.createCell(5);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getSpeciality());//CP_GP_SPL

            l_objCell = l_objRow.createCell(6);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getActualpractice());// Actual practise

            l_objCell = l_objRow.createCell(7);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getPreviousmet());// Lastmet

            l_objCell = l_objRow.createCell(8);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getLastmet());// Previousmet

        }
        // Write OutPut Stream
        try {
                out = new FileOutputStream(m_objFile);
                outBf = new BufferedOutputStream(out);
                m_objWorkBook.write(outBf);
            } catch (Exception ioe) {
            ioe.printStackTrace();
            System.out.println(" Exception in chunk write");
        } finally {
            if (outBf != null) {
                outBf.flush();
                outBf.close();
                out.close();

                l_objRow=null;
                l_objCell=null;
            }

        }


    }

I have searched for Heap space error lot of people suggests to increase JVM memoery using -Xmx options. But i have some limitations so could not use that way. — PrashantK
– PrashantK, Commented Mar 24, 2011 at 7:18
@Suresh S If it doesn't work for more than 23K, 25K batches won't work. — Thomas
– Thomas, Commented Mar 24, 2011 at 7:25
Just another question: What are the limitations that prevent you from increasing memory? — Thomas
– Thomas, Commented Mar 24, 2011 at 7:32
@Thomas My code will be deployed on client server, i cant opt to increase memory for my piece of code. I think as a programmer i should work around my code. — PrashantK
– PrashantK, Commented Mar 25, 2011 at 11:18

Community · Accepted Answer · 2017-05-23 11:59:20Z

2

Instead of populating the complete list in memory before starting to write to excel you need to modify the code to work in such a way that each object is written to a file as it is read from the database. Take a look at this question to get some idea of the other approach.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 24, 2011 at 7:30

gkamal

21k4 gold badges62 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PrashantK Over a year ago

i cannot immediately write objects to File, because i need to compare to objects for business logic thats why i kept those objects in a Map. I get certain set of data like 200 records from database at a time and i need to process those records and once done i can send a map to write to a file.

Thomas · Accepted Answer · 2011-03-24 07:32:01Z

0

Well, I'm not sure if POI can handle incremental updates but if so you might want to write chunks of say 10000 Rows to the file. If not, you might have to use CSV instead (so no formatting) or increase memory.

The problem is that you need to make objects written to the file elligible for garbage collection (no references from a live thread anymore) before writing the file is finished (before all rows have been generated and written to the file).

Edit:

If can you write smaller chunks of data to the file you'd also have to only load the necessary chunks from the db. So it doesn't make sense to load 50000 records at once and then try and write 5 chunks of 10000, since those 50000 records are likely to consume a lot of memory already.

edited Mar 24, 2011 at 7:32

answered Mar 24, 2011 at 7:24

Thomas

88.9k13 gold badges126 silver badges162 bronze badges

1 Comment

PrashantK Over a year ago

Thank you, First point is quite good, I have modified the code such a way that after one object is used (for writing) i immediately set that obj=null. I hope it works. For Second point i some limitations in certain sense that, i fetch some numbers of records process those records in business logic and set those value to VOs(Value objects) thats why i need to maintain a Map for comparison with previous objects.

JenEriC · Accepted Answer · 2011-03-24 07:34:16Z

0

As Thomas points out, you have too many objects taking up too much space, and need a way to reduce that. There is a couple of strategies for this I can think of:

Do you need to create a new factory each time in the loop, or can you reuse it?
Can you start with a loop getting the information you need into a new structure, and then discarding the old one?
Can you split the processing into a thread chain, sending information forwards to the next step, avoiding building a large memory consuming structure at all?

answered Mar 24, 2011 at 7:34

JenEriC

7587 silver badges10 bronze badges

Collectives™ on Stack Overflow

Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related