wood burning stoves*
The moose likes Performance and the fly likes Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet." Watch "Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet." New topic
Author

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

Prashant Kadake
Greenhorn

Joined: Apr 09, 2009
Posts: 3
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:



Please help me with the solution.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42359
    
  64
Yep, POI uses a lot of memory when dealing with large spreadsheets. Having to up the memory is a common occurrence.


Ping & DNS - my free Android networking tools app
Prashant Kadake
Greenhorn

Joined: Apr 09, 2009
Posts: 3
@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.
Sai Hegde
security forum advocate
Ranch Hand

Joined: Oct 26, 2010
Posts: 200
    
    1

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.
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12809
    
    5
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
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18708
    
    8

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.
Sumit Bisht
Ranch Hand

Joined: Jul 02, 2008
Posts: 329

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.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
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.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
NetBeans has a high-end powerful Java Profiler suited for analyzing a JRE. Check out the details:

http://netbeans.org/features/java/profiler.html
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java Heap Space Error, OutofMemory Exception while writing large data to excel sheet.