So I'm working on a method that needs to generate a potentially giant SQL script, so of COURSE I'm NOT going to use ColdFusion's string concatenation functionality...far too slow. In other words, I will NOT be doing this:
<cfset thestring = "" />
<cfloop query="qryData">
<cfset thestring = thestring & "," & theField />
</cfloop>
So, the next obvious choice is to leverage the amazingly fast java stringBuffer object! I've used it before, and I know for a fact that it is an extremely high speed approach to building strings. However, there is another approach I use in PHP (since I don't have easy access to Java, that I am aware of), that also works very fast. So, I thought I'd implement it using ColdFusion as well and make it run a foot race with stringBuffer to see which was actually faster. Before I go on, let me demonstrate this other method I'm talking about.
<cfset aString = arraynew(1) />
<cfloop query="qryData">
<cfset arrayAppend(aString,"check this value out: " & theField) />
</cfloop>
<cfset finalString = arrayToList(aString,"<br>") />
In a nutshell, for each line in my final string, I append an array item. When the array is fully populated, I simply turn it back into a list, using a line break as my delimiter. In PHP I would "implode" the array, but arrayToList accomplishes the same thing.
Here is the code we'll be using to do the same thing with stringBuffer:
<cfset csvstr = createObject("java","java.lang.StringBuffer")>
<cfloop query="qryData">
<cfset csvstr.append("check this value out: " & theField & "<br>") />
</cfloop>
<cfset finalString = csvstr.toString() />
Here are the results of running 40 tests over a 50,000 record query:
I think the moral of this story is obvious ;)
Here is the actual code I used to conduct the tests:
<cfparam name="application.counter" default=0 />
<cfparam name="application.jtotal" default=0 />
<cfparam name="application.atotal" default=0 />
<cfparam name="application.jAverage" default=0 />
<cfparam name="application.aAverage" default=0 />
<cfparam name="reset" default=false />
<cfif reset>
<cfset application.counter = 0 />
<cfset application.jtotal = 0 />
<cfset application.atotal = 0 />
<cfset application.jAverage = 0 />
<cfset application.aAverage = 0 />
</cfif>
<!--- increment my counter --->
<cfset application.counter = application.counter + 1 />
<!--- execute query to retrieve 50K records --->
<cfquery name="qryData" datasource="glock">
select invoicenum,mid(comment,1,50) as comment from ilog_text LIMIT 0,50000
</cfquery>
<cfoutput>
<!--- LET'S DO THE STRINGBUFFER METHOD --->
<!--- create stringbuffer object --->
<cfset csvstr = createObject("java","java.lang.StringBuffer")>
<!--- add initial header line --->
<cfset csvstr.append("invoicenum,comment" & "<br>" )>
<!--- start our counter... --->
<cfset jstart = getTickCount() />
<!--- loop over the query results and build the string --->
<cfloop query="qryData">
<cfset csvstr.append(invoicenum & "," & comment & "<br>")>
</cfloop>
<!--- generate the final string... --->
<cfset finalstring = csvstr.toString() />
<!--- stop the clock --->
<cfset jend = getTickCount() />
<!--- capture stringBuffer stats --->
<cfset thisJTime = jend-jstart />
<cfset application.jtotal = application.jtotal + thisJTime />
<cfset application.jAverage = application.jtotal/application.counter />
<!--- LET'S DO TH ARRAY METHOD... --->
<!--- create our string array --->
<cfset aLines = arraynew(1) />
<!--- add header line --->
<cfset arrayappend(aLines,"invoicenum,comment") />
<!--- start our counter --->
<cfset astart = getTickCount() />
<!--- loop over the query results and build our array of strings --->
<cfloop query="qryData">
<cfset arrayappend(aLines,invoicenum & "," & comment)>
</cfloop>
<cfset delim = "<br>" />
<!--- generate the final string... --->
<cfset finalstring = arrayToList(aLines,delim) />
<!--- stop the clock --->
<cfset aend = getTickCount() />
<!--- capture and output array method stats --->
<cfset thisATime = aend-astart />
<cfset application.atotal = application.atotal + thisATime />
<cfset application.aAverage = application.atotal/application.counter />
<!--- OUTPUT THE RESULTS TO THE SCREEN --->
Records being parsed: #qryData.recordcount# Number of executions: #application.counter#<hr>
(time in milliseconds)<hr>
THIS JAVA TIME: #thisJTime# AVERAGE JAVA TIME: #application.jAverage#<hr>
THIS ARRAY TIME: #thisATime# AVERAGE ARRAY TIME: #application.aAverage#<hr>
Array method is #decimalformat(application.aAverage/(application.aAverage + application.jAverage)) * 100#% faster!
</cfoutput>
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Anyhow here's my modification of the test, hopefully the cf code will come through your filter:
<!--- CFSAVECONTENT METHOD... --->
<!--- start counter --->
<cfset astart = getTickCount() />
<!--- loop over the query results and build the final variable --->
<cfsavecontent variable="finalstring">
invoicenum,comment<br />
<cfoutput query="qryData">#qryData.invoicenum#, #comment#<br/></cfoutput>
</cfsavecontent>
<!--- stop the clock --->
<cfset aend = getTickCount() />
<!--- capture and output array method stats --->
<cfset thisATime = aend-astart />
<cfset application.atotal = application.atotal + thisATime />
<cfset application.aAverage = application.atotal/application.counter />
cfsavecontent = 37.1ms average
ArrayAppend = 45.9ms average
The reason I tested it is because I wanted to do another test - is there a difference between:
StringBuffer.append(qryData.invoicenum & "," & qryData.comment & "
")
vs
StringBuffer.append("#qryData.invoicenum#,#qryData.comment#
")
Well, there is, but only ~5ms (67.9ms -> 63.3ms) on average.
Here's the code I used for all of this: https://gist.github.com/922346
Unless this is something *really* time critical, go with whichever method you find easiest to maintain.
The 5ms in my test is because I accidentally used the (non-threadsafe) StringBuilder class, instead of StringBuffer.