In an effort to resolve memory and performance problems with generating large CSV and tab delimited files in an application I wrote at Duke, I started hunting around for solutions.

Initially, I was using the java stringbuffer method, but found that it's really hard to be sure that CF doesn't use String objects, especially when doing things like calling out to an external function to perform formatting (ie, if the field is a string, then surround it with double quotes and escape any internal double quotes).

A simple file drop of 7200 rows and 140 columns took 68 seconds and sucked a lot of memory.  And no, it wasn't the file writing that caused the problem, it was the call out to the formatting function.

If I performed the same drop using the tab delimited format, I didn't have to call out to that function, but the drop still took 30 seconds.  I needed it to be faster because some of the drops my users perform are much much larger.

so I started hunting around for a java-based solution and found the JavaCSV library:

• Java CSV Homepage:
  http://www.csvreader.com/java_csv.php
• JavaCSV Sourceforge Project Page:
  http://sourceforge.net/projects/javacsv/

After installing this library in my C:\Jrun4\servers\myInstance\cfusion.ear\cfusion.ear\WEB-INF/cfusion/lib directory and restarting Coldfusion, I was able to use the following code to generate my CSV files:

The same drop which had previously taken 68 seconds now only took 18 seconds - AND used considerably less memory.

As you can see, the code handles both CSV and tab-delimited formats AND handles the proper escaping of strings containing delimiters as well.

Many CF programmers out there know that coldfusion uses java string objects to store its variables usually.  And since java strings are "immutable", every time you change it, a new string is created.

If you find yourself doing huge amounts of string concatenations, you'll often see people suggesting that you look up the java StringBuffer object and use that instead.  That would allow you to append to a single StringBuffer object rather than creation a million string objects.

But there's another solution, apparently.

CFSAVECONTENT is so ridiculously fast compared to the old string concatenation method with CFSET that it has got to be using a StringBuffer behind the scenes.  At least, that's what I'm thinking.

Take the following code, for example.  On my local machine, the CFSET method took 64 seconds to complete.  The CFSAVECONTENT method completed in a mere 203ms.

Also, the memory consumption of the CFSET method was significant, while the CFSAVECONTENT method was hardly noticeable.

http://www.compoundtheory.com/?action=asynchttp.index

A question was posted on the cf-talk list (thread) about reading large files with CFFILE and problems they were having.

I suggested trying java to read the large file line by line and I posted the following code:

The code above was tested on CFMX 7 and it does work.  On my server, it consistently returns the results in about 400ms (ranging between 350ms and 500ms).

In order to compare, I wrote some CFML code that does essentially the same thing using CFFILE and looping through the file content as a list with chr(10) as the delimiter.

The CFFILE route was slower and much more erratic, ranging from 450ms to over 2000ms - probably averaging 1400ms in the 20-30 times I reloaded the page.

So if you're reading a large file and doing line by line processing - consider using native java rather than CFFILE.