Duplicate() Much Faster Than ColdFusion Query-of-Queries
Posted July 11, 2006 at 7:50 AM
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:
- ColdFusion's Duplicate() Method
- ColdFusion's Query of Queries
- 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
Comments (0) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
There are no comments posted for this web log entry.
