Stillnet Studios

I recently wrote a routine to export a client’s newsletter subscribers as a CSV file. There are a little over 7000 records in the database now and that is expected to increase at a steady pace. I have been wanting a good reason to reach into Java and do some fancy string manipulation, as I’ve read it can be quite a bit faster than in native CFML. So this looked like a perfect chance to try it. Here’s my first take in plain old CF. I had some additional quote and comma checking in there but I’ve left it out for readability.

<cfset csvstr = "Email Address,First Name,Last Name" & Chr(13) & Chr(10)>
<cfloop query="qrycowusers">
	<cfset csvstr = csvstr & email & ",">

	<cfset csvstr = csvstr & fname & ",">

	<cfset csvstr = csvstr & lname>

	<cfset csvstr = csvstr & Chr(13) & Chr(10)>
</cfloop>

I pushed this code to production and ran it on the larger, production database. It took just over 70 seconds!. Thats a long time for the client to be waiting for their CSV file. Next I rewrote this using Java’s StringBuffer class.

<cfset csvstr = createObject("java","java.lang.StringBuffer")>
<cfset csvstr.append("Email Address,First Name,Last Name" & Chr(13) & Chr(10) )>

<cfloop query="qrycowusers">
	<cfset csvstr.append(email & ",")>

	<cfset csvstr.append(fname & ",")>

	<cfset csvstr.append(lname)>

	<cfset csvstr.append(Chr(13) & Chr(10))>
</cfloop>

I copied this to production and ran it. It took less than 1 second. I ran both versions many times to be sure, and the results were consistent. I was expecting it to be faster, but not that much faster. I think the CF version may have been so extremely slow because I was doing 4 separate cfsets there. If I did it all with one it may have been a good deal faster. But I wanted to have a cfif around each set that checked for the existence of a comma, and if it found one to surround the value with double quotes.

The reason the Java way is faster is because ColdFusion strings are based on the Java String object. Java strings are immutable, that means they can’t be modified. So every time you do something like mystr = mystr & "appended content", ColdFusion is creating a new String object. As you can imagine, this can be very slow once to get into a something that ends up creating a lot of String objects.

I did run into one snag that took a minute to work around. I wanted to output this chunk of csv data to the browser as a downloadable file. This is usually done by by writing the data to a file, then using cfcontent to serve it. But I wanted to skip the writing to disk step. New in CFMX 7 was the “variable” attribute of cfcontent. That allows you to pass data to cfcontent and it will serve it to the browser with whatever mime type you specify. This is great for dynamically creating an image (with cfchart for example), then serving it to the browser. But it only works with binary data. Ok – we can work with that. So my pure ColdFusion version used this:

<cfheader name="Content-Disposition"
value="attachment; filename=ReportUsersExport.csv">
<cfcontent type="application/msexcel"
variable="#ToBinary( ToBase64(csvstr) )#" reset="Yes">

But that won’t work with our StringBuffer object – CF will throw an error about not being able to convert that object to binary. The solution is to use the StringBuffer’s toString() method to turn it into what ColdFusion is expecting:

<cfheader name="Content-Disposition"
value="attachment; filename=ReportUsersExport.csv">
<cfcontent type="application/msexcel"
variable="#ToBinary( ToBase64(csvstr.toString()) )#" reset="Yes">

For small amounts of data, this Java hybrid is of no consequence. On my development box with 5 records, the two files took almost the exact same amount of time. In fact the Java version might even have been slower. But when you get to a larger record set, this can save you a lot of time.