You are not logged in.
Hi,
what I'm trying to achieve is to export a large table (about 2 million rows, 10 columns) from my Oracle-DB to an .xlsx-Report. Therefore, I created an JXLS2-Template with 3 sheets, each containing an foreach-jdbc-query selecting a part of the data so that 1 million limit per sheet is not exceeded. If I limit the source table to 400.000 rows this works fine: The first sheet gets filled in about 5 minutes and the other two stay empty. However, if I increase the size to e.g. 1.2 million, the generation doesn't come to an end and after about 30 minutes the maximum available memory for the report server (20 GB) is occupied and everything freezes. I hoped that activating the new jxls-streaming-feature of RS 3.2 (my current version) might help but it doenst seem to have an effect on the performance in this case. I also tried to schedule the report to let it run in the background but at some point my RS freezes. I can't find any error messages in the logs.
Any advice how I can improve the performance and create an Excel-report with more than 1 million rows?
Thanks in advance for your help!
Last edited by dave (2021-01-09 18:12:05)
Offline
Hi dave,
I hoped that activating the new jxls-streaming-feature of RS 3.2 (my current version) might help but it doenst seem to have an effect on the performance in this case.
activating jxls-streaming should have an effect, but this effect may be not sufficient in your case. Are you sure jxls is activated correctly? Do you see any differences in memory consumption, even if it is not enough or do you see the same memory consumption in both cases?
Regards,
Eduardo
Offline
Hi Eduardo,
I added the property "jxls_stream" with value "true" to the report properties tab. Inside my JXLS2 report template I used a jx:each-statement with a jdbc-query inside like
jx:area(lastCell="L1")
jx:each(items="jdbc.query('Select ranl, partner_nr, partner_name, antnr, auftr, foepnr, sbewart, xbewart, dfaell, bnwhr, doe, plan From FACT_BEWDAT Where rownum < 1000000')" var="d" lastCell="L1")
I allowed 20 GB of memory. In both cases (with or without property jxls_stream) the memory runs full at some point and cpu usage jumps up to 90-100%. I expected that with streaming the whole report would not be generated in memory, but it seems so.
In the release notes i read that there a other streaming-parameters: "Allow to configure jxls2 report streaming parameters: jxls_stream_row_access_window_size, jxls_stream_compress_tmp_files, jxls_stream_use_shared_strings_table". I not sure how to use them. Might that help?
Regards,
dave
Last edited by dave (2021-01-11 11:15:15)
Offline
Hi,
I found out that an export via dynamic lists without a jxls2-Template is much faster and consumes a lot less memory. Even when I'm trying to export more than 1 million rows, it is theoretically working. The only problem is that one sheet cannot handle this amount of rows and an exception occurs. If it would be automatically split into multiple sheets, this would be what I'm looking for. As soon as I add a jxls2-Template to the dynamic list with "jx:each(items="data" var="d" lastCell="L1")" the performance problem is back. Seems like the standard dynamic list is using a faster export algorithm.
Regards
dave
Offline
Hi dave,
jxls-streaming is our first attempt to support large jxls export, and it may be not sufficient in some cases as in your case. This is a known issue and we have a ticket for this.
Ok, if the approach using dynamic lists is better, it may be a good feature request to allow to export more than the max permitted rows in a sheet. So when the row counter gets to the max value, the export would continue in a second sheet, etc.
I raised RS-4625 for this.
As a workaround, you could try with a script that uses jxls (with streaming) directly. Maybe this has a better performance, but we haven't tried this for such large row number https://reportserver.net/en/guides/scri … Executors/
Regards,
Eduardo
Offline
Hi,
thanks for raising the new feature request. I will have a look at the proposed scripting functionality.
Regards
dave
Offline