1

Edit: i changed the name as there is a similar SO question How do I fix SpreadSheetAddRows function crashing when adding a large query? out there that describes my issue so i pharased more succinctly...the issue is spreadsheetAddrows for my query result bombs the entire server at what i consider a moderate size (1600 rows, 27 columns) but that sounds considerably less than his 18,000 rows

I am using an oracle stored procedure accessed via coldfusion 9.0.1 cfstoredproc that on completion creates a spreadsheet for the user to download

The issue is that result sets greater than say 1200 rows are returning a 500 internal server error, 700 rows return fine, so i am guessing it is a memory problem?

the only message i received other than 500 Internal server error in the standard coldfusion look was in small print "gc overhead limit exceeded" and that was only once on a page refresh, which refers to the underlying Java JVM

I am not even sure how to go about diagnosing this

here is the end of the cfstoredproc and spreadsheet obj 

 <!--- variables assigned correctly above --->
 <cfprocresult name="RC1"> 
 </cfstoredproc>

 <cfset sObj = spreadsheetNew("reconcile","yes")>
 <cfset SpreadsheetAddRow(sObj, "Column_1, ... , Column27")>

 <cfset SpreadsheetFormatRow(sObj, {bold=TRUE, alignment="center"}, 1)>

 <cfset spreadsheetAddRows(sObj, RC1)>
    <cfheader name="content-disposition" value="attachment; filename=report_#Dateformat(NOW(),"MMDDYYYY")#.xlsx">
 <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadsheetReadBinary(sObj)#">
Community
  • 1
  • 1
Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71
  • Are you always getting the `gc overhead limit exceeded` error? That error indicates that the JVM is spending too much time in garbage collection. If you can allocate more memory to the JVM it will remedy that issue. http://www.oracle.com/technetwork/java/javase/gc-tuning-6-140523.html – Miguel-F Apr 11 '13 at 11:53
  • No, i only got that once out of like 40 tries, we bumped up the JVM 3x, issue still applies – Jay Rizzi Apr 11 '13 at 14:29

1 Answers1

0

My Answer lies with coldfusion and one simple fact: DO NOT USE SpreadsheetAddRows or any of those related functions like SpreadsheetFormatRows

My solution to this was to execute the query, create an xls file, use the tag cfspreadsheet to write to the newly created xls file, then serve to the browser, deleting after serving

Using SpreadsheetAddRows, Runtime went from crashing server on 1000+ rows, 5+mins on 700 rows Using the method outlined above 1-1.5 secs

if you are interested in more code, i can provide just comment, i am using the coldbox framework so didnt think the specificness would help just the new workflow

Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71