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 »
10,640 Comments
69,065 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 »
10,640 Comments
69,065 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
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Bartezz
Feb 10, 2012 at 7:21 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
Update! Instead of $(eval(options.insertAfter)).after(data['insertData']); I now use: var ajaxNode = document.createElement('span'); var parent = $(eval(options.insertAfter))[0].parentNode; ... read »
Bartezz
Feb 10, 2012 at 6:18 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
encountered this same, what I consider, jQuery bug last week. I'm building a site in which I load some content via AJAX. This content contains Linkedin share button placeholders which Linkedin API ne ... read »
Drew Wells
Feb 10, 2012 at 11:30 AM
Cross-Origin Resource Sharing (CORS) AJAX Requests Between jQuery And Node.js
After you understand the concepts here, this is an awesome cheatsheet for enabling CORS in just about anything http://enable-cors.org/ ... read »
JM
Feb 10, 2012 at 9:10 AM
My Safari Browser SQLite Database Hello World Example
@Amy, Here is a very good tutorial on how to use JOIN: http://www.sqltutorial.org/sqljoin-innerjoin.aspx ... read »
Stefan Richter
Feb 10, 2012 at 4:42 AM
Building A Twitter-Inspired RESTful API Architecture In ColdFusion
This is great, very useful Ben. I spotted a small typo in the api.cgm listing: <cfthrow type="Unauthroized" /> Cheers Stefan ... read »
Hugh
Feb 9, 2012 at 10:35 PM
CFDirectory Filtering Uses Pipe Character For Multiple Filters (Thanks Steve Withington)
I was wondering if there would be a filter you could apply so that you got everything but what you included in the filter. As in show me all docs that are not a .pdf. ... read »
Stephen
Feb 9, 2012 at 10:29 PM
Learning ColdFusion 9: Application-Specific Data Sources
@Ben, No offence, but if people were really wanting advanced features they would be using a platform like ASP.NET MVC. CFML is so structurally compromised as a tag-based scripting language that ... read »
Nathan
Feb 9, 2012 at 10:03 PM
Subversion - Cleanup Failed To Process The Following Paths
@Leviaguirre, do you still have problems with this? ... read »