Writing Enormous Files Based On Massive Record Sets In ColdFusion

I can't help but think you could have achieved this more easily without horsing around with Java and just using <cffile action="append"> (which will be doing all your stuff "under the hood" anyhow).

The problem from the bloke on CF-TALK is that CF is very ham-fisted when it comes to dealing with large amounts of data (recordsets, file contents, etc). It tries to load it ALL into memory, whether you need it or not (the answer being "not" if you're just spooling the recordset to a file).

My conceptual knowledge of Java is far better than my syntactical one, but I would try to do this by streaming the recordset back from the DB, and streaming THAT to the file. So only the chunk-size you're streaming every takes up memory, not the whole result/file.

Now... you're quite good with your Java... I'm sure you could cook something up that does that?

--
Adam

Adam, you could use the CFFile 'append' method, but that eats up even more memory, mostly from a processing perspective. The company I work for ran into this problem also, as we would build large CSV files from data gained from most of the tables our main application. I compared the Java method you talk to here Ben with the ArrayAppend/ArrayToList method I describe at http://www.philduba.com/index.cfm/2006/10/20/Improving-Performance-Concatenating-Large-Strings.
They came out equivalent in time up to 10 million records being written (at least for our data). If someone doesn't have the Java knowledge, the CF solution might be easier to understand.

Hi Ben.
No, I meant foresake CF completely, and do it all in Java. The only time it becomes useful for the data to be in a CF-recordset-format is if you want to use CF query functions on it. If you're just spooling it to a file: no need.

Perhaps the only time I'd involve CF would be if maybe this would be a common enough task to wrap up in a <cf_query2file> tag or something.

--
Adam

Adam, I think doing the query and file writing in Java would be a good solution, if the knowledge is there to do that. In what I've had to work on that led me to the ArrayAppend idea, the query is being used for other things in the page, not just CSV or file creation. I looked at that thread today and the guy seems to be CF only.

this might sound like a silly comment but when I try to post the code, I get pound signs that I have to delete before I can try to execute the code. Is that something you could fix or am I making some sort of mistake?

I've got a similar problem and need to create a 100+MB text file. Is it possible to stream the query results using java?

Thanks Ben - had this issue myself this will be useful to have a go.

On a side, we are also experiencing some real slow down when adding large numbers of images to a zip file. Do you think this would work on this too?

thanks ben - fyi - implemented the above method and it worked well for me the memory for jrun & sql was very stable. Only thing was it took "a lot" longer to run through. I'm going to test out adding in blocks of rows with cfsavecontent in one go, to see if this works quicker for me.

Thanks again.

I keep getting java.lang.OutOfMemoryError: GC overhead limit exceeded

for even 1000 records.

Thank you so much Ben! This really helped me out today. I just have one question. After the entire process is done and I try to move the file and or delete (outside of cf) it says that the file is open by another program. It lloks like java is not letting go of the file. Is there something I am missing?

Hello Ben,

Just wanted to share a change I did as I needed to write the file set as UTF-8. Below is a simple example of how to do this.

// file to write
fileToWrite = 'c:\testfile.txt';

// create the file
theFile = createObject("java","java.io.File").init(javaCast("string",fileToWrite));

// create my java file writer
fileWriter = createObject("java","java.io.BufferedWriter").init(createObject("java","java.io.OutputStreamWriter").init(createObject("java","java.io.FileOutputStream").init(theFile),"UTF8"));

// create string object
stringToWrite = createObject("java","java.lang.StringBuffer").Init();
stringToWrite.Append('Hello World');

// write line
fileWriter.write(stringToWrite.toString());

// Flush the buffered output stream to make sure there is no straggling buffer data
fileWriter.flush();

Sorry the text did't wrap so here is the cut off piece.

fileWriter = createObject("java","java.io.BufferedWriter").
init(createObject("java","java.io.OutputStreamWriter").
init(createObject("java","java.io.FileOutputStream").
init(theFile),"UTF8"));

So, how would this approach need to change if I need to *return* a CSV file to a user? That is, no external file is currently written. It goes like this:

application.Util.prepareForCSVDownload("My Report.csv");
	report = new components.FooDomain.Report(foo, bar);
	report.export();

<cffunction name="prepareForCSVDownload" returntype="void">
	<cfargument name="filename" required="yes" type="string"/>
 
	<cfsetting enablecfoutputonly="yes" showdebugoutput="no" />
	<cfcontent type="application/msexcel" />
	<cfheader name="Content-Disposition" value='filename="#filename#"' /> <!--- #Replace(filename, " ", "\ ", "all")# --->
	</cffunction>

1. Would you just use Java buffered output to GetPageContext().GetOut()?
2. Probably, you would get rid of GetPageContext().GetOut().ClearBuffer(), right?

I need to find a way of stopping running out of heap for a large CSV report rather urgently. Thanks in advance for any ideas.

I like this post. A lot of really good, useful information here. When I saw the title, I couldn't help but click on it, because it sounds exactly like the kind of random problem I would have. Good job at finding a solution.

Adding to my previous comment, should I just <cfflush> my CSV output and all will be good, at least on the output side? The DB side is a separate issue.

<cfflush interval="512" />

at the top of the page solved my problem and provided nice download progress feedback for users.

However it has one nasty side effect. When IE prompts and I select to save .csv, everything works great. When IE prompts and I select to Open with Excel, it is downloaded with the wrong extension: .csv.XLS, and, as a result, Excel does not recognize CSV format.

OK, next attempt at a solution is putting <cfflush> after the headers. This time Open file works. However, that change introduced 2 problem with report file name:

1. File name suddenly started to contain underscores rather than spaces.

2. Open file generates the file which is shown as reportAction (which is the .CFM name). Weird.

The code now looks like this:

<cfscript>
// download spreadsheet     application.Util.prepareForCSVDownload("My Report.csv");
</cfscript>
<cfflush interval="512" /> <!--- Flush output every 512 bytes --->
<cfscript>
report = new components.FooDomain.Report(foo, bar);
report.export();
</cfscript>

Brilliant! Exactly what I needed. Thanks so much.

On our dev team we always joke about how your blog always shows up in the top five results for our Google searches when we're stuck on something. Thanks for all the help you don't know you've already provided!