Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at RIA Unleashed (Nov. 2010) with:

Array Looping Much Faster Than Query Looping

Posted by Ben Nadel
Tags: ColdFusion

I pretty much guessed that looping over an array in ColdFusion would be faster than looping over a query in ColdFusion. But, to me, looping over a query is SOOO much cleaner looking (only when using the CFLoop tag, not in CFScript tags) than looping over an array that I ran this test in hopes that magically a query loop would be faster. I was thinking about this in terms of creating static collections of data, such as a list of states or a list of countries. Who hasn't had to loop over those at some time?

The following test creates a one-dimensional array and query and just tests how fast it can be iterated over. A query can be looped over in a query loop OR in an index loop, so I tested both of those. But first, I created the data sets:

  • <!--- Set the data size. --->
  • <cfset intDataSize = 10000 />
  •  
  •  
  • <!--- Create a data array. --->
  • <cfset arrGirls = ArrayNew( 1 ) />
  •  
  • <!--- Resize the array. --->
  • <cfset ArrayResize( arrGirls, intDataSize ) />
  •  
  • <!--- Add data to the array. --->
  • <cfloop index="intI" from="1" to="#intDataSize#" step="1">
  •  
  • <!--- Set data item. --->
  • <cfset arrGirls[ intI ] = ListGetAt(
  • "Libby,Marry Kate,Ashley,Azure,Francis,Lori,Alex",
  • RandRange( 1, 7 )
  • ) />
  •  
  • </cfloop>
  •  
  •  
  • <!--- Create the data query. --->
  • <cfset qGirls = QueryNew( "name" ) />
  •  
  • <!--- Add rows to the query. --->
  • <cfset QueryAddRow( qGirls, intDataSize ) />
  •  
  • <!--- Add data to the array. --->
  • <cfloop index="intI" from="1" to="#intDataSize#" step="1">
  •  
  • <!--- Set data item. --->
  • <cfset qGirls[ "name" ][ intI ] = ListGetAt(
  • "Libby,Marry Kate,Ashley,Azure,Francis,Lori,Alex",
  • RandRange( 1, 7 )
  • ) />
  •  
  • </cfloop>

As you can see from above, I am creating a data set that is 10,000 items long. Then, I tested how long each type of loop would take. For the looping, all I did was output the data item value:

  • <!--- Test how long the array loop takes. --->
  • <cftimer label="Array Loop" type="outline">
  •  
  • <!--- Loop over array using an index loop. --->
  • <cfloop index="intI" from="1" to="#ArrayLen( arrGirls )#" step="1">
  • #arrGirls[ intI ]#
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!--- Test how long the query loop takes. --->
  • <cftimer label="Query Loop" type="outline">
  •  
  • <!--- Loop over the query. --->
  • <cfloop query="qGirls">
  • #qGirls.name#
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!--- Test how long the query loop takes when used like an array. --->
  • <cftimer label="Query Loop (as Array)" type="outline">
  •  
  • <!--- Loop over the query using an index loop. --->
  • <cfloop index="intI" from="1" to="#qGirls.RecordCount#" step="1">
  • #qGirls[ "name" ][ intI ]#
  • </cfloop>
  •  
  • </cftimer>

Not suprisingly, the array loop was MUCH faster than either of the query loops. The speed results on average where:

  • Array: 31ms - 90ms
  • Query Loop: 3140ms - 3234ms
  • Query Loop (as Array): 2344ms - 3109ms

As you can see, the array loop is like 50 times faster than the query loop. Keep in mind that this is over 10,000 iterations, but still! That IS much faster. Using the query as a multidimensional array ([column][row]) had some speed improvements, but was still tens of times slower than the array index.

So again, nothing special here. That was pretty much was what I expected (though not quite such a large performance difference). But, I blogged earlier about the array iterator, and was curious how that would perform against the query loop. The iterator loop still looks nicer to me.

  • <!--- Test how long the array loop takes with the iterator. --->
  • <cftimer label="Array Loop (as Iterator)" type="outline">
  •  
  • <!--- Get the array iterator. --->
  • <cfset objIterator = arrGirls.Iterator() />
  •  
  • <!--- Loop over the array while we have items. --->
  • <cfloop condition="objIterator.HasNext()">
  • #objIterator.Next()#
  • </cfloop>
  •  
  • </cftimer>

Suprisingly, even with the added method calls and iterative condition evaluation, the array iterator is still VERY fast. On average it performed as such:

Array Iterator: 78ms - 93ms

As you can see, it performs comparable to the array index loop, but at the slow end of the spectrum. It's still like 50 times faster than the query loop. To me, it just looks very elegant. The one issue I have with it is that you have to create the iterator before you go into the loop. This doesn't look as nice as just looping. So, in a select box output, I might not opt for the iterator (as I am very anal about how my code looks), but if I was in a CFScript tag, I would definitely choose the array iterator over the array index loop as the code looks nice, is more "readable," and is less bulky.

Tweet This Groovy post by @BenNadel - Array Looping Much Faster Than Query Looping Thanks my man — you rock the party that rocks the body!


Reader Comments

I'm getting an error from cfloop query="qGirls" and qGirls.name. I don't see where the qGirls has been created.

Reply to this Comment

Hey Ben,
I truly enjoy your posts. You are one smart guy. One thing I am curious about. I know this is an older post but wouldn't the loop run even quicker if the ArrayLen was calced prior to the loop?

Something like:

<!--- Loop over array using an index loop. --->
<cfset var arrGirlsLength = ArrayLen( arrGirls )>
<cfloop index="intI" from="1" to="#arrGirlsLength#" step="1">
#arrGirls[ intI ]#
</cfloop>

Thanks for all your informative posts.

Reply to this Comment

@Ed,

I appreciate the kind words :) Our code is actually the same as far as evaluation goes. In the tag-based implementation, the TO attribute is only evaluated once. As such, getting the array length outside the tag is no different.

But, that is *only* because my version is tag-based. If you were writing CFScript, then absolutely, you are correct. This *would* be slower:

for (intI = 1 ; intI < arrayLen( arrGirls ) ; intI++)

In that case, as you are saying, getting the array length before the loop would be a much better choice.

Reply to this Comment

  • <cfoutput>
  • <cfset arr = ArrayNew(1)>
  •  
  • <cfloop from="1" to="1000" index="k">
  • <cfset ArrayAppend(arr, "This is my loop testing....")>
  • </cfloop>
  •  
  • <cftimer label="iterator Loop" type="outline">
  • <cfloop from="1" to="#ArrayLen(arr)#" index="i">
  • #arr[i]#<br/>
  • </cfloop>
  • </cftimer>
  •  
  • <cftimer label="Array Loop" type="outline">
  • <cfloop array="#arr#" index="i">
  • #i#<br/>
  • </cfloop>
  • </cftimer>
  •  
  • </cfoutput>
  •  
  • <cfabort>

Output: First loop takes 6ms, second takes 4ms

Conclusion: looping over iterator, is slower then looping over array, but you loose iterator variable if you really need it!

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.