Skip to main content
Participant
February 11, 2010
Question

cfspreadsheet problem with large datasets

  • February 11, 2010
  • 3 replies
  • 9978 views

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.

    This topic has been closed for replies.

    3 replies

    Participant
    February 12, 2010

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

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

    Inspiring
    February 12, 2010

    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

    Adobe Employee
    February 12, 2010

    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

    Participant
    February 12, 2010

    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.

    Participant
    March 9, 2010

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

    Inspiring
    February 12, 2010

    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