Highlighted

CFSPREADSHEET tag & function takes more memory

New Here ,
Oct 04, 2018

Copy link to clipboard

Copied

In our application, we are running a large query with  100k rows and then displaying it in HTML. Then an Export button triggers writing the report to an Excel spreadsheet in the .xlsx format using the <cfspreadsheet> tags. We are finding that the memory usage for creating this report and writing the file jumps the memory usage up by about 1GB. The problem is that it is not released for a while by GC. When we have multiple users running and exporting this same type of report, the memory keeps climbing up and reaches the heap size allocated and kills the serer's performance. A reboot is usually necessary to clear it out. Is this normal expected behavior or how should we be dealing with this issue? Is it possible to easily release the memory usage of this operation after the export has completed so that others running the report readily get access to the freed up space for their reports? Also  SpreadsheetSetCellValue, SpreadsheetFormatCell take much memory.

And the final output file is around 8 MB only.

Views

272

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CFSPREADSHEET tag & function takes more memory

New Here ,
Oct 04, 2018

Copy link to clipboard

Copied

In our application, we are running a large query with  100k rows and then displaying it in HTML. Then an Export button triggers writing the report to an Excel spreadsheet in the .xlsx format using the <cfspreadsheet> tags. We are finding that the memory usage for creating this report and writing the file jumps the memory usage up by about 1GB. The problem is that it is not released for a while by GC. When we have multiple users running and exporting this same type of report, the memory keeps climbing up and reaches the heap size allocated and kills the serer's performance. A reboot is usually necessary to clear it out. Is this normal expected behavior or how should we be dealing with this issue? Is it possible to easily release the memory usage of this operation after the export has completed so that others running the report readily get access to the freed up space for their reports? Also  SpreadsheetSetCellValue, SpreadsheetFormatCell take much memory.

And the final output file is around 8 MB only.

Views

273

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 04, 2018 0
LEGEND ,
Oct 04, 2018

Copy link to clipboard

Copied

The only thing that I can think of is to manually GC the query object and the Excel object after processing.  StructDelete() might work.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 04, 2018 2
Adobe Community Professional ,
Oct 04, 2018

Copy link to clipboard

Copied

WolfShade​'s suggestion to explicitly delete the variables might help. But in case it doesn't, you might also look at changing how aggressive your overall GC settings are in jvm.config, or running a full GC at the end of the report generation. This will probably make the response a bit (or a lot) slower, but it will keep you from having to restart CF.

system = createObject(‘java’, ‘java.lang.System’);

system.runFinalization();

system.gc();

Dave Watts, Fig Leaf Software

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 04, 2018 1
LEGEND ,
Oct 05, 2018

Copy link to clipboard

Copied

https://forums.adobe.com/people/Dave+Watts  wrote

system = createObject(‘java’, ‘java.lang.System’);

system.runFinalization();

system.gc();

Just curious, Dave.  Given that the above code entails "system", would this GC apply only to the application?  Or would it, as the name suggests, GC the entire system?  I _think_ I know the answer, but don't want to assume.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2018 0
Adobe Community Professional ,
Oct 05, 2018

Copy link to clipboard

Copied

This would apply to the virtual machine running this instance of ColdFusion.

Dave Watts, Fig Leaf Software

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2018 0
LEGEND ,
Oct 05, 2018

Copy link to clipboard

Copied

My assessment was completely incorrect.  Did some Google-ing on the subject.  Very informative.

Thanks, Dave.

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 05, 2018 0