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,243 Comments
75,390 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,243 Comments
75,390 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
WebManWalking
May 23, 2013 at 11:06 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, Are you talking about As Number: YES As String: YES As Java: YES? If so, that's with 3 different ways of referencing the constant 1, not users.id[1]. Query object references(*) are what seem ... read »
Ben Nadel
May 23, 2013 at 9:55 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dan, According to the CF Admin, I'm running Java "1.6.0_45". As far as the DB column, in the database it's an INT. I'll see if I can dig into what CF sees it as. @WebManWalking, But h ... read »
WebManWalking
May 23, 2013 at 9:49 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, I think the problem is that we're used to loose typing in ColdFusion, like JavaScript. If a value is a number but it's needed in an expression to be a string, noooo problem. I've encountered ... read »
Heather
May 23, 2013 at 9:47 AM
ColdFusion QueryAppend( qOne, qTwo )
You rock! Thank you, thank you, thank you!!! ... read »
angelo
May 23, 2013 at 5:19 AM
Ask Ben: Print Part Of A Web Page With jQuery
How to print also the background color of table cells and table lines ... read »
Hitesh Agja
May 23, 2013 at 3:55 AM
Javascript Array Methods: Unshift(), Shift(), Push(), And Pop()
very interesting and helpful too. ... read »
Joel
May 22, 2013 at 5:35 PM
Script Tags, jQuery, And Html(), Text() And Contents()
This is still an issue 2 years later. jQuery is supposed to remediate these cross browser issues, no? I have been unable to find any statement from the jQuery team calling this behavior "by de ... read »
Phillip Senn
May 22, 2013 at 12:44 PM
Ask Ben: Query Loop Inside CFScript Tags
In cf10, if you call a function that has: local.result = {}; local.result.msg = ""; local.svc = new query(); local.svc.setSQL("SELECT * FROM..."); local.obj = local.svc.exe ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools