cfspreadsheet problem with large datasets

[kalbster]

We have been testing cfspreadsheet and are experienceing large processing times when usingSpreadsheetAddRows.

Here is a simple example:

<cfset newQuery = queryNew("Column_1, Column_2, Column_3, Column_4")>
<cfloop index="i" from="1" to="4000">
<cfset queryAddRow(newQuery)>
<cfset querySetCell(newQuery, "Column_1", i)>
<cfset querySetCell(newQuery, "Column_2", "0")>
<cfset querySetCell(newQuery, "Column_3", "0")>
<cfset querySetCell(newQuery, "Column_4", "0")>
</cfloop>

<!--- Create new spreadsheet Object and add a row with the column names (takes about 2 ms to run) --->
<cfset ssObj=Spreadsheetnew()>
<cfset SpreadsheetAddRow(ssObj,"Column_1, Column_2, Column_3, Column_4")>

<!---  Adds the rows from the query to the spreadsheet (takes about 15 min to run) --->
<cfset SpreadsheetAddRows(ssObj,newQuery)>

<!--- Write spreadsheet --->
<cfspreadsheet action="write" filename="#ExpandPath("./spreadsheets/spreadsheet.xls")#" name="ssObj" sheet=1 sheetname="Test" overwrite=true>

This is running on windows 2K8, IIS 7. I have tried updating Openoffice to 3.2 and also to the newest java SDK. My jrun process will go from 500MB to over 1 gig, and then chug along for 15 minutes before writing the final xls.

[Adam Cameron.]

Interesting.

I modified your code slightly to accept a number of rows to create, as well as timing the process.

Here are my findings:

   1218ms to write 125 rows
   4640ms to write 250 rows
  18343ms to write 500 rows
  75098ms to write 1000 rows
289481ms to write 2000 rows
1205532ms to write 4000 rows

It seems like as one double the number of rows to create, the time to run multiples by (say) 4x.  That ain't good.

--

Adam

[ksaini]

We have already a bug(81154) logged for this. This is fixed and will be released with CF9 updater.

Thanks

Kunal Saini

Adobe ColdFusion Team

[brian_kalbfleisch]

Are there any workarounds or hot fixes that can be applied? Or is there a planned release date for CF9 updater? This is one of the main reasons we upgraded to CF9.

[Ken_Reiss_-_PortONE]

Yes, what is the planned release date for the CF9 Updater?  This is really a serious issue!

[JEFFMACE]

Can anyone confirm that the CFSpreadsheet performance increased with the release of the updater?  The server I am on does not have the latest updates and I am not sure when they will udpate it, but if I try to write 5000+ rows to a spreadsheet (5-10 columns), JRUN shoots through the roof and never comes down making and I will need to restart JRUN.

I hope this has helped with performance and if so, I may be able to get them to speed up the update process.

Thanks!

[ksaini]

CFSpreadsheet performance of formatting functions (SpreadSheetFormatrow/s, SpreadSheetFormatcolumn/s, SpreadSheetFormatcell) increased with CF9 updater.

Also a new function is added SpreadSheetFormatCellRange to increase performance of formatting multiple cells.

Also there is an improvement in performance of SpreadsheetAddRows.

Thanks

Kunal Saini

Adobe ColdFusion Team

[JEFFMACE]

Sorry to report, cfspreadsheet does not work any better.  Trying to export 7 columns of basic information (First name, last name, email, phone, etc)  Its about 80,000 rows, which i uderstand is rather large.  Just doing a write to spreadsheet from a query:

<cfspreadsheet
          action = "write"
          filename="#pathPreVar#mr\call\cc_export_files\#fileNameVarReads#"
          query="getData"
          overwrite="true">

I watch JRUN go from 580mb to 2.2GB in about 10 seconds, then I get an error after about a minutes:

Java heap space null

java.lang.OutOfMemoryError: Java heap space at java.util.ArrayList.<init>(ArrayList.java:112) at java.util.ArrayList.<init>(ArrayList.java:119) at org.apache.xmlbeans.impl.store.Saver.<init>(Saver.java:86) at org.apache.xmlbeans.impl.store.Saver$TextSaver.<init>(Saver.java:896) at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546) at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436) at org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1455) at org.apache.xmlbeans.impl.values.XmlObjectBase.toString(XmlObjectBase.java:1440) at org.apache.poi.xssf.model.SharedStringsTable.addEntry(SharedStringsTable.java:161) at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:321) at coldfusion.excel.Excel.writeExcel(Excel.java:4694) at coldfusion.tagext.lang.SpreadSheetTag.writeToExcel(SpreadSheetTag.java:217) at coldfusion.tagext.lang.SpreadSheetTag.doStartTag(SpreadSheetTag.java:178) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722) at cfcall_center_export2ecfm561437795.runPage(C:\mr2\call_center_export.cfm:38) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:381) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:94) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:79) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:200) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)

[Ken_Reiss_-_PortONE]

I still have the same problem too. Until they fix it we upgraded to 16gb ram. Sucks though!

Sent from my iPhone

[Dave Watts]

Are you using a 64-bit version of CF? Because otherwise, you really can't take advantage of that 16GB of RAM.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[Ken_Reiss_-_PortONE]

Yes. 16 bit. Ken.

Sent from my iPhone

[Ken_Reiss_-_PortONE]

Sorry. Meant 64. Had a brain lapse back to the old 8 bit computing days when 16 bit intel 286 were new!

Sent from my iPhone

[JEFFMACE]

Yes, those were the good old days.  We are using 64-bit as well.  CFspreadsheet was one of the most exciting features of going to CF 9 for the department I am in, but it is beyond frustrating when something like this can kill your system.  I used a CFX tag and the performance is excellent, but it only works up to the 65,000 row range.  So this was super exciting to me when I learned that cfspreadsheet would work beyond that.  Its performance when exporting 10,000 rows really isn't even that good though, it never releases the memory it used.

[ksaini]

Hi THEIDEABULB,

Let me try this scenario. What is the Xmx value you are using to run this?

Thanks

Kunal

[JEFFMACE]

Right now its set to 2048

[ksaini]

The OOM exception is caused by underlined POI libraries. Please log a bug for this, we will investigate if we can improve on this.

Thanks

Kunal

[Gneisler]

As of 2014, this is still an issue with our implementation, which is probably due to us still using CF 9.0.2.

If this is fixed, can you please provide details about in which version was this is corrected?

Thank you!

[brian_kalbfleisch]

For anyone else interested, here it the url for the bug.

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=81154

[Adam Cameron.]

justwanttoreadforums wrote:

For anyone else interested, here it the url for the bug.

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=81154

I've voted for it 🙂

--

Adam