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

Hi,
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;
            }

}

Please help me with the solution.

I dont want to use JVM -XMX options to increase memory.

Why not? It sounds like you probably need to increase the heap.

Yep, POI uses a lot of memory when dealing with large spreadsheets. Having to up the memory is a common occurrence.

@Paul Sturrock I think i should work on code, optimized enough to cope with default memory of JVM. Having good hope that some engineering can save me.! can you please go through the code and suggest me some good practises to handle memory usages.
@Ulf Dittmer May be POI can be the reason for outOfMemory, but can you please go through the code and suggest me some good practises to handle memory usages.

I havent run through your code but good design patterns most of the times should keep you from falling into such exceptions.
Did you run your code against a fairly smaller amount of data in your excel sheet. Use the Flyweight Pattern to create your objects if possible.

How about writing the data as CSV lines and stream them to the user? Very low memory use and much faster than POI object creation. Excel should be happy to read CSV and build the spreadsheet.

There appears to be no real reason to build POI objects.

Bill

Prashant Kadake wrote:I think i should work on code, optimized enough to cope with default memory of JVM.

Are you're suggesting that it should be possible to modify any code whatsoever to run in any limited amount of memory? That may be true in theory but it may require a lot of work in real life. For example POI is written to keep its data in memory, so the more data you give it, the more memory it uses. It might be possible to modify POI to use temporary disk files (for example) as a backing store, but that would probably take an experienced programmer several months to do.

Prashant, I am not familiar with POI, but it looks as if you are using the entire dataset in a single chunk. Can you try doing that in different steps ?
Also, have a look at Weakly referenced (http://download.oracle.com/javase/1.4.2/docs/api/java/lang/ref/WeakReference.html) datastructures in this case.

I don't see any mention of it in this thread, but have you analyzed the JRE's memory usage in detail. The details of this analysis would help identify where and if you have memory leaks in your code and where you can possibly rewrite the code to be more efficient.

Eclipse has a Memory Analysis Tool which explains how to get a memory dump and how to use this to track where all of the memory is taken up.
http://www.eclipse.org/mat/

Saying that you 'don't want to increase memory' may not be an option. Sometimes if the application needs more memory, it needs more memory.

NetBeans has a high-end powerful Java Profiler suited for analyzing a JRE. Check out the details:

http://netbeans.org/features/java/profiler.html