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 Scotch On The Rock (SOTR) 2010 (Brussels) with:

Duplicate() Much Faster Than ColdFusion Query-of-Queries

By Ben Nadel on
Tags: ColdFusion, SQL

For some giggles, I decided to test the speed of different query duplication methods in ColdFusion. Right off the top of my head I though of three ways to duplicate a query:

  1. ColdFusion's Duplicate() Method
  2. ColdFusion's Query of Queries
  3. Building the query from scratch, value by value

So far starters, I built a simple query that I was going to duplicate:

  • <cfset qTest = QueryNew( "id, name, hair, curvey, hot" ) />
  •  
  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "1" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "vivenzio, sarah" />
  • <cfset qTest[ "hair" ][ qTest.RecordCount ] = "brunette" />
  • <cfset qTest[ "curvey" ][ qTest.RecordCount ] = "yes" />
  • <cfset qTest[ "hot" ][ qTest.RecordCount ] = "yes" />
  •  
  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "2" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "thomas, ashley" />
  • <cfset qTest[ "hair" ][ qTest.RecordCount ] = "brunette" />
  • <cfset qTest[ "curvey" ][ qTest.RecordCount ] = "yes" />
  • <cfset qTest[ "hot" ][ qTest.RecordCount ] = "yes" />
  •  
  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "3" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "monique" />
  • <cfset qTest[ "hair" ][ qTest.RecordCount ] = "brunette" />
  • <cfset qTest[ "curvey" ][ qTest.RecordCount ] = "yes" />
  • <cfset qTest[ "hot" ][ qTest.RecordCount ] = "no" />
  •  
  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "4" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "knight, kathleen" />
  • <cfset qTest[ "hair" ][ qTest.RecordCount ] = "blonde" />
  • <cfset qTest[ "curvey" ][ qTest.RecordCount ] = "no" />
  • <cfset qTest[ "hot" ][ qTest.RecordCount ] = "no" />

Now, I ran each type of query duplication for 100 iterations so that the time differences were more pronounced. I used the ColdFusion CFTimer tag with an outlined display. I also outputted "iteration #i#" for each iteration so that there was some visual feed back.

First, I used the Duplicate() method. This requires one line of code and is built into ColdFusion, so, hopefully it should be very fast:

  • <cftimer label="Using Duplicate" type="outline">
  •  
  • <cfloop index="i" from="1" to="100" step="1">
  •  
  • <cfset VARIABLES["qTest" & i] = Duplicate( qTest ) />
  •  
  • iteration <cfoutput>#i#</cfoutput>,
  •  
  • </cfloop>
  •  
  • </cftimer>

Duplicate() runs 100 iterations consistently at 0ms. Wow, that's fast. Let's see how it compares to other methods. Next, I tried using ColdFusion's query of query capabilities thinking I could easily duplicate the query with a SELECT *:

  • <cftimer label="Using Query of Queries" type="outline">
  •  
    <cfloop index="i" from="1" to="100" step="1">
  •  
    <!--- Query for all records of the test query. --->
  • <cfquery name="qTest#i#" dbtype="query">
  • SELECT
  • *
  • FROM
  • qTest
  • </cfquery>

  • iteration <cfoutput>#i#</cfoutput>,
  •  
  • </cfloop>
  •  
  • </cftimer>

The query of query method runs consistently between 500ms and 600ms, but would occassionally spike to up over 1000ms. Compared to Duplicate() this is MUCH slower. But it makes sense I suppose. For each iteration of the loop, ColdFusion is parsing a SQL string, optimizing it, reading every row from another query, and inserting that row into a new query. So maybe it's the SQL that is slowing it down. Let's see what happens if I do the same thing but building the query up from scratch without query of queries:

  • <cftimer label="Building From the Ground-Up" type="outline">
  •  
  • <cfloop index="i" from="1" to="100" step="1">
  •  
  • <cfset VARIABLES["qTest" & i] = QueryNew( qTest.ColumnList ) />
  •  
  • <!--- Loop over the test query. --->
  • <cfloop query="qTest">
  •  
  • <!--- Create a new row for this query record. --->
  • <cfset QueryAddRow( VARIABLES["qTest" & i] ) />
  •  
  • <!--- Loop over the column list and set each value. --->
  • <cfloop index="column" list="#qTest.ColumnList#">
  • <cfset VARIABLES["qTest" & i][ column ][ qTest.CurrentRow ] = qTest[ column ][ qTest.CurrentRow ] />
  • </cfloop>
  •  
  • </cfloop>
  •  
  • iteration <cfoutput>#i#</cfoutput>,
  •  
  • </cfloop>
  •  
  • </cftimer>

Building the new query from the ground-up ran very consistently at around 350ms. Consistently faster than the query of queries but still hugely slow compared to ColdFusion's built-in Duplicate() method. Well that's cool. It's nice to know that the most simple solutions (one line of code for query duplication) is also the fastest by a long shot. I don't know how variables work under the ColdFusion hood, when you get down into the nitty-gritty of their Java base classes, but it's obvious that allowing ColdFusion to do what it knows best is the best solution here.

And just a personal note, while query of queries is clearly the weakest method of query duplication, I still think that query of queries are amazing and totally awesome and were some of the best features of the ColdFusion MX family.



Reader Comments

I am working on a massive xml parsing, qofq app to create 2 seperate xml files.

I just don't understand the concept/purpose of duplicate function, are you duplicating the data or the row, into a new query?

So basically you are copying from 1 query to another?

Just makes me wonder what are faster ways to manipulate the data, that is also easy to understand :)

Not 2 things i see happen a lot lol...

Btw huge fan of your blog, and your work. Keep up the great job...

Reply to this Comment

Interesting how much faster that is, but I guess it makes sense. It's not doing any processing.

Surprised this didn't get more comments.

Craig, duplicate() is a 'safe' way of duplicating an entire variable.

There some some odd circumstances where, say
<cfset x = "myVar">
<cfset y = x>
can result in modifications to variable y changing the value of x as well. Using
<cfset x = "myVar">
<cfset y = duplicate(x)>
prevents that.

And of course, if you want to modify the value of X and compare it against the original afterwards, duplicate is an easy way to ensure a clean copy.

That said, I've been doing this a few months shy of 10 years and have only run into one instance where duplicate was *required*, and that ended up in a bug report.

It involved performing a query of queries that joined two query objects (sounds silly, I know, but one was from sql and the other was built on the fly from another source). After the Q of Q was done, the original query object "forgot" what it was sorted by. Using duplicate() on the query object before the Q of Q ensured a copy that wasn't modified.

Have you used it for much, Ben? Maybe I'm missing out on some way it'd be useful more frequently.

Reply to this Comment

@JC,

I have run into some cases (from what I can remember) where I was trying to copy a single-depth struct and structCopy() was not working. In that case, I fell back on duplicate(). I was probably messing something up, but it seemed that structCopy() was only doing a reference copy, not a key copy. Maybe I'll do some more digging into that.

As far as query of query forgetting sorting, I think I have heard that this a bug in ColdFusion. I believe I read it on Ray Camden's blog a while back.

Reply to this Comment

Yeah, that was the same issue -- I emailed Ray when it happened, he's the one who filed the bug report.

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.