You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Matt Rogghe <mr...@blazent.com> on 2012/01/20 23:39:51 UTC
SXSSF Writing - Memory issues
Howdy all,
Looking simply for any tips when writing huge workbooks using the SXSSFWorkbook implementation. I'm pulling data from a database and writing out a workbook. In this particular case I'm writing upwards of 3+ million rows across 5 sheets. Using the SXSSF implementation (MUCH better than XSSF obviously) I can nearly complete the operation. Memory appears to quickly reach 2GB useage for the process but performance degrades gradually until I get a java.lang.OutOfMemoryError: GC overhead limit exceeded error.
Are there any quick wins I should be looking for? In the latest incarnation of my code (of which there have been many) I am manually flushing 200 rows at a time with the initial workbook constructor instantiated with the -1 (disable auto flushing) flag.
I'm thinking maybe just up the Java memory footprint but... was hoping to keep a more compact JVM using SXSSF.
Any thoughts?
Thanks,
-Matt
RE: SXSSF Writing - Memory issues
Posted by Matt Rogghe <mr...@blazent.com>.
Apologies for the delay in responding... and thank you both for the responses.
You both (sight unseen even) pointed out the quick win and a very novice mistake I made. I was creating a large number of CellStyle objects. I changed this to a collection object which is initially populated with known styles I need and later referenced to get the appropriate style. The application screams through several million records now in roughly 32 seconds and a maximum of 1.3GB of memory used by the JVM in total.
One additional question: I know that SXSSF is intended for writing only. What is the accepted/expected means of appending data into an existing workbook? For my purposes I need to overwrite the contents of a certain number of sheets. My current process:
1. Open the existing workbook as an XSSFWorkbook.
2. Delete the sheets I will be overwriting.
3. Write those changes out to disk and close the objects.
4. Re-open the XSSFWorkbook (are these two steps necessary? I am erring on the side of caution making sure the workbook objects are written and refreshed before doing my mass data load).
5. Open an SXSSFWorkbook passing in the XSSFWorkbook object to the constructor.
6. Run through my database to Excel export process.
7. Write, flush and close objects.
Is this, in your view, the proper method of modifying and/or appending data into an existing workbook using SXSSF?
-----Original Message-----
From: Alex Geller [mailto:ag@4js.com]
Sent: Monday, January 23, 2012 12:40 PM
To: user@poi.apache.org
Subject: Re: SXSSF Writing - Memory issues
Hi,
I make sure that the cell styles that I register are unique and I wouldn't
expect to have more than a few hundred at most. But even if the sheet
contains thousands of different colors/font combinations that isn't likely
to be the reason that you run out of memory. You can however run into
trouble if you use merged regions (Sheet.addMergedRegion()) because these
are kept in memory and the numbers can be quite large. If for example you
have two cells that are merged per row then you will have as many entries as
rows. This issue could be fixed by writing the file containing the merged
regions in streaming manner the same way we are writing the data file. If
this isn't the issue then I suggest to analyze the heap (e.g. use jhat) to
see what classes are at the top of the heap histogram.
Regards,
Alex
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-Writing-Memory-issues-tp5161879p5166819.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: SXSSF Writing - Memory issues
Posted by Alex Geller <ag...@4js.com>.
Hi,
I make sure that the cell styles that I register are unique and I wouldn't
expect to have more than a few hundred at most. But even if the sheet
contains thousands of different colors/font combinations that isn't likely
to be the reason that you run out of memory. You can however run into
trouble if you use merged regions (Sheet.addMergedRegion()) because these
are kept in memory and the numbers can be quite large. If for example you
have two cells that are merged per row then you will have as many entries as
rows. This issue could be fixed by writing the file containing the merged
regions in streaming manner the same way we are writing the data file. If
this isn't the issue then I suggest to analyze the heap (e.g. use jhat) to
see what classes are at the top of the heap histogram.
Regards,
Alex
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-Writing-Memory-issues-tp5161879p5166819.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: SXSSF Writing - Memory issues
Posted by Jim Talbut <jt...@spudsoft.co.uk>.
On 20/01/2012 22:39, Matt Rogghe wrote:
> Howdy all,
>
> Looking simply for any tips when writing huge workbooks using the SXSSFWorkbook implementation. I'm pulling data from a database and writing out a workbook. In this particular case I'm writing upwards of 3+ million rows across 5 sheets. Using the SXSSF implementation (MUCH better than XSSF obviously) I can nearly complete the operation. Memory appears to quickly reach 2GB useage for the process but performance degrades gradually until I get a java.lang.OutOfMemoryError: GC overhead limit exceeded error.
>
> Are there any quick wins I should be looking for? In the latest incarnation of my code (of which there have been many) I am manually flushing 200 rows at a time with the initial workbook constructor instantiated with the -1 (disable auto flushing) flag.
>
> I'm thinking maybe just up the Java memory footprint but... was hoping to keep a more compact JVM using SXSSF.
>
> Any thoughts?
>
> Thanks,
> -Matt
>
A few thoughts that might help:
Are you sure that you aren't loading your entire query resultset into
memory?
This is the default for JDBC, and ir;s very unhealthy with large resultsets.
SXSSF only streams the cell contents, are you doing anything else per
cell that might allocate memory?
The worst example would be creating a CellStyle per cell.
Can you publish the code you use to process each row?
Are you able to profile your code?
Jim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org