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

Posted July 11, 2006 at 7:50 AM

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:

 Launch code in new window » Download code as text file »

  • <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:

 Launch code in new window » Download code as text file »

  • <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 *:

 Launch code in new window » Download code as text file »

  • <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:

 Launch code in new window » Download code as text file »

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

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Other Searches  |  Print Page




Reader Comments

Mar 18, 2010 at 11:48 AM // reply »
13 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...


Mar 19, 2010 at 9:36 AM // reply »
7,572 Comments

@Craig,

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


Post Comment  |  Ask Ben

Recent Blog Comments
Mar 20, 2010 at 12:07 PM
Drawing On The iPhone Canvas With jQuery And ColdFusion
Simply awesome. Saved my day. ... read »
Mar 20, 2010 at 9:00 AM
Building A Fixed-Position Bottom Menu Bar (ala FaceBook)
I would like to say thx for an easy way to create a bottom bar. I do have a ?. Is it possible to center the bar if i want to resize it to ex 85%. Regards Offenbach ... read »
Mar 19, 2010 at 7:26 PM
MySQL 3/4 - com.mysql.jdbc.Driver And allowMultiQueries=true
Thank you very much for this post. Adding allowMultiQueries="true" in context.xml didn't help until I added it to url as allowMultiQueries=true Good idea is to use prepared statements and it will he ... read »
Jim
Mar 19, 2010 at 4:49 PM
Nobody Puts Baby In The Corner!
Wow. This is like suddenly finding a support group for your secret shame. I'm not alone! I always liked this movie, even though it is extremely cheesy. I just wish Jennifer Grey hadn't gotten the ... read »
Mar 19, 2010 at 4:47 PM
Application.cfc OnRequest() Method Affects OnError() Arguments
@Jason and @Ben, I've been doing some CF9 refactoring on our systems and noticed an odd occurrence with onError as well. Found a way to work around my problem, but what I saw was... Background: Our ... read »
Jim
Mar 19, 2010 at 4:44 PM
Shoot 'Em Up Starring Clive Owen And Paul Giamatti
I actually enjoyed this movie quite a lot. It was different, certainly, but I think they were going for more of a Quentin Tarentino-"wow, that was weird"-vibe than an actual spoof. Once I realize ... read »
Mar 19, 2010 at 4:34 PM
An Intensive Exploration Of jQuery With Ben Nadel (Video Presentation)
Hey I guess the video is down. Is there anyway you can upload to youtube or vimeo or some other service? Greatly appreciated. ... read »
Mar 19, 2010 at 4:24 PM
ColdFusion CFPOP - My First Look
@Ben Thanks for the follow up! The root of the problem had to do with being able to trace bounced emails to specific records in a DB table. Let's say you run an email campaign and you get 1,000 bou ... read »