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

Posted July 11, 2006 at 7:50 AM by Ben Nadel

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

Craig M. Rosenblum
Mar 18, 2010 at 11:48 AM // reply »
23 Comments
188 Points

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...


Ben Nadel
Mar 19, 2010 at 9:36 AM // reply »
11,314 Comments
76,113 Points

@Craig,

Thanks my man - glad you're liking the blog :)


JC
Apr 2, 2010 at 7:20 AM // reply »
16 Comments
169 Points

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.


Ben Nadel
Apr 2, 2010 at 9:45 AM // reply »
11,314 Comments
76,113 Points

@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.


JC
Apr 2, 2010 at 11:54 AM // reply »
16 Comments
169 Points

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


Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
seb
Jun 20, 2013 at 2:32 AM
Working With Inherited Collections In AngularJS
@mike, @ben, The best article about scope and prototypal prototypical inheritance in angularjs is http://stackoverflow.com/questions/14049480/what-are-the-nuances-of-scope-prototypal-prototypical- ... read »
Josh
Jun 20, 2013 at 2:17 AM
ColdFusion NumberFormat() Exploration
Nice read thanks Ben, Is there a way to mask a negative number? Long story short in the finance sector when you go 'short' on a stock you want the price to fall this is a good thing because you are ... read »
mohsen
Jun 20, 2013 at 1:09 AM
The Beauty Of The jQuery Each() Method
my html code : <html> <head> <script type="text/javascript" src="jquery.js"></script> <script type="text/javascript" src="nss.js"> ... read »
Alesei Narkevitch
Jun 19, 2013 at 11:31 PM
Directive Link, $observe, And $watch Functions Execute Inside An AngularJS Context
@Ben, bunch to learn indeed, but thats fun part : ) ... read »
Burdock-roots
Jun 19, 2013 at 10:41 PM
Referencing ColdFusion Query Columns In A Loop Using Both Array And Dot Notation
Burdock-roots Are you going fat day by day? You need to be good for your family and make some money too. So we bring for you a best product that helps you to be more energetic every day. You will b ... read »
Diego Vilar
Jun 19, 2013 at 9:52 PM
Working With Inherited Collections In AngularJS
I recognize the applicability of your solution, and how easy it makes to share data across multiple views or even "submodules" of rather simple application. But it seems to me that it creat ... read »
Ben Nadel
Jun 19, 2013 at 9:38 PM
Directive Link, $observe, And $watch Functions Execute Inside An AngularJS Context
@Alesei, Glad you like it. Even after working with AngularJS for months, I still get a bunch of unexpected, "$digest is already in progress". So hard to debug sometimes! ... read »
Ben Nadel
Jun 19, 2013 at 9:36 PM
Working With Inherited Collections In AngularJS
@Mike, The relationship of $scope values is definitely an interesting thing! But it's not simple - it really forces you to understand prototypal inheritance, which is not at all a simple topic! Gla ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools