Is there a limit to the number of rows that can be inserted into the Excel file?
I have a query that returns > 140'000 rowes and then my report crashes wit the following error:
Error running report: GC overhead limit exceeded
Performance info for Report Report 47081
Report 47081 : 347455 ms
Parsing command : 0 ms
Preparing Data : 19 ms
Generating Excel Document : 347415 ms
Filling Excel Template : 347415 ms
Running SQL 'SELECT * FROM HEL_SERVICE_COST_CALCULATION_V ...' (113749 records) : 10522 ms
When I limit the output toomethingg less than 100'000 the report runs OK. Is there a way to get more than 100'000 rows into the Excel? If yes, what needs to be done? Increase the Garbage collector?
Best regards
Jean-Paul Hahn
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If you're using XSLX format the limit is around 1 million rows if I remember correctly, so that's not the root cause here. The problem you're having here is that your Excel document is too large to fit into memory, which results in the GC releasing less and less memory with each run as your heap is being filled with the Excel data.If you hadn't run in the "GC overhead limit exceeded" first, it's likely there's an "out of heap space" error not far down the road.
I can only see two solutions here:
1) Increase your Heap size. This is the simplest solution (memory is cheap), but is not always an option if you're not in control of the hardware or JVM configuration that runs your report.
2) Give up using JETT, and as a matter of fact just give up using Apache POI to fully load the XLSX document in memory. You'll need to insert your data in the XLSX file in a streamed fashion, with only a small part of the document loaded in memory at a time. You can google SXSSF for more info. Of course, this approach is only viable if you have very basic data insert requirements that can work with SXSSF limitations. If you're just outputing the results of SQL query in a large Excel sheet, it should work fine.
Cheers,
Etienne.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In the meantime I read more into HP's PPMC Excel Report Cookbook and stumbled over the large data concept. Is that part of standard JETT?
The query in Excel looks as follows.
${{ largeData = reportingdb.execQuery('SELECT * FROM HEL_SERVICE_COST_CALCULATION_V'); return '';}}
and the different fields are adressed with: $$ TASKNAME $$. It is alos important to have ne empty column to the left and a space after and before the $$
Now the report ran within 12.5 seconds and returned 140222 rows!
I also tried to post that response into the forum, but I got a spambot error twice :-(
Best regards
Jean-Paul Hahn
-----Original Message-----
From: Etienne Canaud [mailto:etiennecanaud@users.sf.net]
Sent: Dienstag, 3. Oktober 2017 15:58
To: [jett:discussion]
Subject: [jett:discussion] Limit to excel rows?
Hi Jean-Paul,
If you're using XSLX format the limit is around 1 million rows if I remember correctly, so that's not the root cause here. The problem you're having here is that your Excel document is too large to fit into memory, which results in the GC releasing less and less memory with each run as your heap is being filled with the Excel data.If you hadn't run in the "GC overhead limit exceeded" first, it's likely there's an "out of heap space" error not far down the road.
I can only see two solutions here:
1) Increase your Heap size. This is the simplest solution (memory is cheap), but is not always an option if you're not in control of the hardware or JVM configuration that runs your report.
2) Give up using JETT, and as a matter of fact just give up using Apache POI to fully load the XLSX document in memory. You'll need to insert your data in the XLSX file in a streamed fashion, with only a small part of the document loaded in memory at a time. You can google SXSSF for more info. Of course, this approach is only viable if you have very basic data insert requirements that can work with SXSSF limitations. If you're just outputing the results of SQL query in a large Excel sheet, it should work fine.
If you're using (Micro Focus) PPM Excel Reports then yes, using Large Data
mode is the right thing to do - I implemented it just for this purpose. If
I remember correctly I wrote in the Cookbook that you should use large data
mode as soon as you have more than a few thousands rows to insert, and over
100k definitely matches this description :) Glad to know it works as
intended.
And no, Large Data mode is not a part of JETT. Since JETT is very powerful
in what it can do with the template, it always needs to get the whole
document loaded in memory, thus these memory problems when working on very
large documents. FYI, PPM 9.4X has some improvements on Excel Reports, it
gives you access to the whole Excel file in SXSSF format (not just the
first sheet), so you can insert large amount of data on any sheet, and also
include some formatting (to the extent of what SXSSF allows). It requires
you to write some Java code though, so if you just need to insert lots of
data on the first sheet, standard Large data mode should be enough.
In the meantime I read more into HP's PPMC Excel Report Cookbook and
stumbled over the large data concept. Is that part of standard JETT?
The query in Excel looks as follows.
${{ largeData = reportingdb.execQuery('SELECT * FROM
HEL_SERVICE_COST_CALCULATION_V'); return '';}}
and the different fields are adressed with: $$ TASKNAME $$. It is alos
important to have ne empty column to the left and a space after and before
the $$
Now the report ran within 12.5 seconds and returned 140222 rows!
I also tried to post that response into the forum, but I got a spambot
error twice :-(
Best regards
Jean-Paul Hahn
-----Original Message-----
From: Etienne Canaud [mailto:etiennecanaud@users.sf.net]
Sent: Dienstag, 3. Oktober 2017 15:58
To: [jett:discussion]
Subject: [jett:discussion] Limit to excel rows?
Hi Jean-Paul,
If you're using XSLX format the limit is around 1 million rows if I
remember correctly, so that's not the root cause here. The problem you're
having here is that your Excel document is too large to fit into memory,
which results in the GC releasing less and less memory with each run as
your heap is being filled with the Excel data.If you hadn't run in the "GC
overhead limit exceeded" first, it's likely there's an "out of heap space"
error not far down the road.
I can only see two solutions here:
1) Increase your Heap size. This is the simplest solution (memory is
cheap), but is not always an option if you're not in control of the
hardware or JVM configuration that runs your report.
2) Give up using JETT, and as a matter of fact just give up using Apache
POI to fully load the XLSX document in memory. You'll need to insert your
data in the XLSX file in a streamed fashion, with only a small part of the
document loaded in memory at a time. You can google SXSSF for more info. Of
course, this approach is only viable if you have very basic data insert
requirements that can work with SXSSF limitations. If you're just outputing
the results of SQL query in a large Excel sheet, it should work fine.
In the meantime I read more into HP's PPMC Excel Report Cookbook and stumbled over the large data concept. Is that part of standard JETT?
The query in Excel looks as follows.
${{ largeData = reportingdb.execQuery('SELECT * FROM HEL_SERVICE_COST_CALCULATION_V'); return '';}}
and the different fields are adressed with: $$ TASKNAME $$. It is alos important to have ne empty column to the left and a space after and before the $$
Now the report ran within 12.5 seconds and returned 140222 rows!
Best regards
Jean-Paul Hahn
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello
Is there a limit to the number of rows that can be inserted into the Excel file?
I have a query that returns > 140'000 rowes and then my report crashes wit the following error:
Error running report: GC overhead limit exceeded
Performance info for Report Report 47081
Report 47081 : 347455 ms
Parsing command : 0 ms
Preparing Data : 19 ms
Generating Excel Document : 347415 ms
Filling Excel Template : 347415 ms
Running SQL 'SELECT * FROM HEL_SERVICE_COST_CALCULATION_V ...' (113749 records) : 10522 ms
When I limit the output toomethingg less than 100'000 the report runs OK. Is there a way to get more than 100'000 rows into the Excel? If yes, what needs to be done? Increase the Garbage collector?
Best regards
Jean-Paul Hahn
Hi Jean-Paul,
If you're using XSLX format the limit is around 1 million rows if I remember correctly, so that's not the root cause here. The problem you're having here is that your Excel document is too large to fit into memory, which results in the GC releasing less and less memory with each run as your heap is being filled with the Excel data.If you hadn't run in the "GC overhead limit exceeded" first, it's likely there's an "out of heap space" error not far down the road.
I can only see two solutions here:
1) Increase your Heap size. This is the simplest solution (memory is cheap), but is not always an option if you're not in control of the hardware or JVM configuration that runs your report.
2) Give up using JETT, and as a matter of fact just give up using Apache POI to fully load the XLSX document in memory. You'll need to insert your data in the XLSX file in a streamed fashion, with only a small part of the document loaded in memory at a time. You can google SXSSF for more info. Of course, this approach is only viable if you have very basic data insert requirements that can work with SXSSF limitations. If you're just outputing the results of SQL query in a large Excel sheet, it should work fine.
Cheers,
Etienne.
Hi Etienne
Thank you very much for your quick response.
In the meantime I read more into HP's PPMC Excel Report Cookbook and stumbled over the large data concept. Is that part of standard JETT?
The query in Excel looks as follows.
${{ largeData = reportingdb.execQuery('SELECT * FROM HEL_SERVICE_COST_CALCULATION_V'); return '';}}
and the different fields are adressed with: $$ TASKNAME $$. It is alos important to have ne empty column to the left and a space after and before the $$
Now the report ran within 12.5 seconds and returned 140222 rows!
I also tried to post that response into the forum, but I got a spambot error twice :-(
Best regards
Jean-Paul Hahn
-----Original Message-----
From: Etienne Canaud [mailto:etiennecanaud@users.sf.net]
Sent: Dienstag, 3. Oktober 2017 15:58
To: [jett:discussion]
Subject: [jett:discussion] Limit to excel rows?
Hi Jean-Paul,
If you're using XSLX format the limit is around 1 million rows if I remember correctly, so that's not the root cause here. The problem you're having here is that your Excel document is too large to fit into memory, which results in the GC releasing less and less memory with each run as your heap is being filled with the Excel data.If you hadn't run in the "GC overhead limit exceeded" first, it's likely there's an "out of heap space" error not far down the road.
I can only see two solutions here:
1) Increase your Heap size. This is the simplest solution (memory is cheap), but is not always an option if you're not in control of the hardware or JVM configuration that runs your report.
2) Give up using JETT, and as a matter of fact just give up using Apache POI to fully load the XLSX document in memory. You'll need to insert your data in the XLSX file in a streamed fashion, with only a small part of the document loaded in memory at a time. You can google SXSSF for more info. Of course, this approach is only viable if you have very basic data insert requirements that can work with SXSSF limitations. If you're just outputing the results of SQL query in a large Excel sheet, it should work fine.
Cheers,
Etienne.
Limit to excel rows?
Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/jett/discussion/general/
To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/
Hi Jean-Paul,
If you're using (Micro Focus) PPM Excel Reports then yes, using Large Data
mode is the right thing to do - I implemented it just for this purpose. If
I remember correctly I wrote in the Cookbook that you should use large data
mode as soon as you have more than a few thousands rows to insert, and over
100k definitely matches this description :) Glad to know it works as
intended.
And no, Large Data mode is not a part of JETT. Since JETT is very powerful
in what it can do with the template, it always needs to get the whole
document loaded in memory, thus these memory problems when working on very
large documents. FYI, PPM 9.4X has some improvements on Excel Reports, it
gives you access to the whole Excel file in SXSSF format (not just the
first sheet), so you can insert large amount of data on any sheet, and also
include some formatting (to the extent of what SXSSF allows). It requires
you to write some Java code though, so if you just need to insert lots of
data on the first sheet, standard Large data mode should be enough.
Cheers,
Etienne.
On Wed, Oct 4, 2017 at 12:05 AM, Jean-Paul Hahn jphahn01@users.sf.net
wrote:
Hi Etienne
Thank you very much for the additional explanations.
You did a great job by implementing the Large Data model for PPM.
Best regards
Jean-Paul
Hi Etienne
Thank you very much for your quick response.
In the meantime I read more into HP's PPMC Excel Report Cookbook and stumbled over the large data concept. Is that part of standard JETT?
The query in Excel looks as follows.
${{ largeData = reportingdb.execQuery('SELECT * FROM HEL_SERVICE_COST_CALCULATION_V'); return '';}}
and the different fields are adressed with: $$ TASKNAME $$. It is alos important to have ne empty column to the left and a space after and before the $$
Now the report ran within 12.5 seconds and returned 140222 rows!
Best regards
Jean-Paul Hahn