B4J Question jPOI- Things to know when dealing with Big data

[incendio]

Hello guys,

I have app that export data from database server to excel file. Data has 49 columns.

When data only a few hundreds records, the app worked as expected, but when it reach about +/- 200K records, it raised an error :

#
# A fatal error has been detected by the Java Runtime Environment:
#
# EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x00000000626cd331, pid=980, tid=0x00000000000001b0
#
# JRE version: Java(TM) SE Runtime Environment (8.0_92-b14) (build 1.8.0_92-b14)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.92-b14 mixed mode windows-amd64 compressed oops)
# Problematic frame:
# V [jvm.dll+0x3fd331]
#
# Failed to write core dump. Minidumps are not enabled by default on client versions of Windows
#
# An error report file with more information is saved as:
# D:\Data\B4J\ExportXL\Objects\hs_err_pid980.log
#
# If you would like to submit a bug report, please visit:
# http://bugreport.java.com/bugreport/crash.jsp
#

App used connection pool to collect data.
Attached is an error report file.

Any idea how to fix this error?

Thanks in advance.

 

[incendio]

I manage to re-use style objects.

App can runs OK if write to a new workbook, RAM usage are low, but this way, loose other features that already saved in a Template, such as charts.

Have tried many time to modify existing workbook, but it always ended crashed due to massive RAM usage.

No other way to modify existing workbook without crashed?

 

[incendio]

Just a final conclusion :

1. On my case jPOI was able to handle 200K data & probably lots more if it was created on a new WorkBook
2. Modifying existing Workbook with large data, depend on the size of rows & columns is out of question. My guest is, if you have less than 10K rows & 50 columns, you can probably success to do it. More than this, it could ended in a crash. RAM used will increase fast and eventually will make app crash.
   The example codes on #9, if you modified it to modify existing workbook instead of create new one, will crash.
3. With Firebird server, using SQL query from jSQL, though I only issued 1 query on my app, viewing activity of Firebird Service on Windows Task Manager, seems that query running twice, with make retrieving large data took more time. Don't know what causing this, could be JDBC, jSQL, or others.

Thanks for all who have give suggestion/hint/idea.

 

[OliverA]

Failed to read zip entry source

Looking at the POI source (here, just search for "Failed to read zip entry source") it looks like POI is having an issue reading the file. You could try saving it in an older format and see if POI has the same issues.

Tested with real data(about 200K rows), mostly, it worked, RAM usage also only about 200MB, but only if this codes removed

Try to declare XLBorderS as a class/global variable and initialize/populate it only once (outside of this method). Right now it looks like the code is creating a new cell style for each row and once you hit 64K rows, you'll seem to run out of cell styles. It looks like (from this method at least) that you're just using one row style for all/most of the rows (instead of each row having its own).

 

[incendio]

Looking at the POI source (here, just search for "Failed to read zip entry source") it looks like POI is having an issue reading the file. You could try saving it in an older format and see if POI has the same issues.


Try to declare XLBorderS as a class/global variable and initialize/populate it only once (outside of this method). Right now it looks like the code is creating a new cell style for each row and once you hit 64K rows, you'll seem to run out of cell styles. It looks like (from this method at least) that you're just using one row style for all/most of the rows (instead of each row having its own).

It has already solved, the idea was the same with you, thanks anyway