Getting Out of memory error while writing data into CSV file

Question

I am trying to export data in a CSV file, there are 100k rows to be exported. I am getting java.lang.OutOfMemoryError: GC overhead limit exceeded error and other warning messages.

I have increased the heap memory to 8GB but still getting the out-of-memory error. Please help if any code change needs to be done or to look at the server configuration settings.

I am using SpringMVC4, Java 1.8 and Tomcat version 7 for the development.

I have a UI page with an image icon (export to excel) to download the report

Steps for loading data into CSV file:

Data pull from database table using PreparedStatement.
Condition applied to skip the rows.
Data are appended in an object array.
Header columns are formed using a PreparedStatement (the header columns vary based on step1).
Data load to CSV file from the object array and writing to the file using XSSFWorkbook.

Getting out of memory error in step5 while writing the data into the CSV file.

Code Snippet

//fetch data
String query="select partid, partnumber, partname from tparts";
      List<Map<String, Object>> rows = jdbcTemplate.queryForList(query);
       for(Map<String, Object> row : rows){
          String strPartId = row.get("partid").toString();
          String strPartName = (String)row.get("partname");
          }
 
//xls download
public byte[] ExportPartsData(PartsRequestVO reqVo, PartsDataVo partsDataVo) throws Exception {
    XSSFWorkbook xssWrkBook = new XSSFWorkbook();
    XSSFSheet xssSheet = xssWrkBook.createSheet();
     
    List<Object[]> lstObj = partsDataVo.getPartsData();
     
    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    xssWrkBook.write(outStream);
    return outStream.toByteArray();

Error Log

Handler processing failed; nested exception is java.lang.OutOfMemoryError: GC overhead limit exceeded at org.springframework.web.servlet.DispatcherServlet.triggerAfterCompletionWithError(DispatcherServlet.java:1303) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:977) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.serviceInternal(HttpServlet.java:741) at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498)

WARN JDBC Connection to reset not identical to originally prepared Connection - please make sure to use connection release mode ON_CLOSE (the default) and to run against Hibernate 4.2+ (or switch HibernateJpaDialect’s prepareConnection flag to false

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread

Can't your database just export to csv without going through Java? If not, look into pagination. — Robert
– Robert, Commented Apr 8, 2022 at 21:05

Mostafa Saied · Accepted Answer · 2022-04-08 23:00:46Z

1

Try using Spring batch excel: https://github.com/spring-projects/spring-batch-extensions/tree/main/spring-batch-excel

You can use StreamingXlsxItemReader it's made for reading big data files.

Quote from project README below:

To reduce the memory footprint the StreamingXlsxItemReader can be used, this will only keep the current row in memory and discard it afterward. Not everything is supported while streaming the XLSX file. It can be that formulas don’t get evaluated or lead to an error.

answered Apr 8, 2022 at 23:00

Mostafa Saied

431 silver badge6 bronze badges

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

Comments

Sam Ginrich · Accepted Answer · 2022-04-08 23:10:48Z

0

From your log it's not visible, which call the exception invokes. Assume it is

xssWrkBook.write(outStream);

Consider, that ByteArrayOutputStream is limited to 2GB and in case of the SUN runtime it's allocation concept is not optimized. You anyway want to write to a CSV file!?

answered Apr 8, 2022 at 23:10

Sam Ginrich

8872 gold badges9 silver badges12 bronze badges

Collectives™ on Stack Overflow

Getting Out of memory error while writing data into CSV file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related