Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with:

ColdFusion Query Of Queries Speed And Database Calls

By Ben Nadel on
Tags: ColdFusion

Let me preface this saying that I think ColdFusion query of queries are just freakin' awesome. They make things really easy, elegant, and sexy. That being said, one of the biggest benefits of the query of queries is that they do not need to hit the SQL server or other database systems when querying for data. The down side to this, however, is that ColdFusion must do what the SQL server does. SQL server is optimized for data retrieval. That is WHAT IT LIVES FOR. This means that ColdFusion query of queries, while less intensive on the DB is going to be more intensive on the ColdFusion server and possibly slower.

To experiment, I have taken a situation where I might want to run a query of queries. Let's take outputting all blog entries on a site accompanied by the tags for that entry (This could be accomplished via a CFOutput / Group tag, but I am not a big fan of that). I am going to do this the traditional way and then I am going to try two different query of query methods:

  • <!--- Test the tranditional database call. --->
  • <cftimer label="Repeat Database Reads" type="outline">
  •  
  • <!--- Query for entries. --->
  • <cfquery name="qEntry" datasource="...">
  • SELECT
  • b.id,
  • b.name,
  • b.date_posted
  • FROM
  • blog_entry b
  • ORDER BY
  • b.date_posted DESC,
  • b.time_posted DESC,
  • b.id DESC
  • </cfquery>
  •  
  • <!--- Output entries. --->
  • <cfloop query="qEntry">
  •  
  • <!--- For EACH entry, query for tags for this entry. --->
  • <cfquery name="qTag" datasource="...">
  • SELECT
  • t.id,
  • t.name
  • FROM
  • tag t
  • INNER JOIN
  • blog_entry_tag_jn btjn
  • ON
  • (
  • t.id = btjn.tag_id
  • AND
  • btjn.blog_entry_id = <cfqueryparam value="#qEntry.id#" cfsqltype="CF_SQL_INTEGER" />
  • )
  • ORDER BY
  • t.name ASC
  • </cfquery>
  •  
  • <!--- Output the entry name. --->
  • <p>
  • #qEntry.name#
  • </p>
  •  
  • <!--- Get value list of tags. --->
  • <p>
  • #ValueList( qTag.name, ", " )#
  • </p>
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!--- Test ColdFusion query of queries using one table. --->
  • <cftimer label="Query of Queries" type="outline">
  •  
  • <!--- Query for entries. --->
  • <cfquery name="qEntry" datasource="...">
  • SELECT
  • b.id,
  • b.name,
  • b.date_posted
  • FROM
  • blog_entry b
  • ORDER BY
  • b.date_posted DESC,
  • b.time_posted DESC,
  • b.id DESC
  • </cfquery>
  •  
  • <!--- Query for all the tags. --->
  • <cfquery name="qAllTags" datasource="...">
  • SELECT
  • t.id,
  • t.name,
  • btjn.blog_entry_id
  • FROM
  • tag t
  • INNER JOIN
  • blog_entry_tag_jn btjn
  • ON
  • (
  • t.id = btjn.tag_id
  • AND
  • btjn.blog_entry_id IN ( #ValueList( qEntry.id )# )
  • )
  • </cfquery>
  •  
  • <!--- Output entries. --->
  • <cfloop query="qEntry">
  •  
  • <!--- For EACH entry, query for tags for this entry. --->
  • <cfquery name="qTag" dbtype="query">
  • SELECT
  • id,
  • name
  • FROM
  • qAllTags
  • WHERE
  • blog_entry_id = <cfqueryparam value="#qEntry.id#" cfsqltype="CF_SQL_INTEGER" />
  • ORDER BY
  • name ASC
  • </cfquery>
  •  
  • <!--- Output the entry name. --->
  • <p>
  • #qEntry.name#
  • </p>
  •  
  • <!--- Get value list of tags. --->
  • <p>
  • #ValueList( qTag.name, ", " )#
  • </p>
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!---
  • Test ColdFusion query of queries using two
  • tables and a join execution.
  • --->
  • <cftimer label="Query of Queries (JOIN)" type="outline">
  •  
  • <!--- Query for entries. --->
  • <cfquery name="qEntry" datasource="...">
  • SELECT
  • b.id,
  • b.name,
  • b.date_posted
  • FROM
  • blog_entry b
  • ORDER BY
  • b.date_posted DESC,
  • b.time_posted DESC,
  • b.id DESC
  • </cfquery>
  •  
  • <!---
  • Query for all the entry-tag joins. This will
  • return just the entry ID and the tag ID to
  • join on later.
  • --->
  • <cfquery name="qEntryTagJN" datasource="...">
  • SELECT
  • ( t.id ) AS tag_id,
  • btjn.blog_entry_id
  • FROM
  • tag t
  • INNER JOIN
  • blog_entry_tag_jn btjn
  • ON
  • (
  • t.id = btjn.tag_id
  • AND
  • btjn.blog_entry_id IN
  • (
  • #ValueList( qEntry.id )#
  • )
  • )
  • </cfquery>
  •  
  • <!--- Query for simple list of tags. --->
  • <cfquery name="qAllTags" datasource="...">
  • SELECT
  • t.id,
  • t.name
  • FROM
  • tag t
  • </cfquery>
  •  
  • <!--- Output entries. --->
  • <cfloop query="qEntry">
  •  
  • <!---
  • For EACH entry, query for tags for this entry.
  • This will be done by joining the simple tag
  • list with the join table we created above.
  • --->
  • <cfquery name="qTag" dbtype="query">
  • SELECT
  • qAllTags.id,
  • qAllTags.name
  • FROM
  • qAllTags,
  • qEntryTagJN
  • WHERE
  • <!--- Join the two tables on tag ID. --->
  • qAllTags.id = qEntryTagJN.tag_id
  • AND
  • qEntryTagJN.blog_entry_id = <cfqueryparam value="#qEntry.id#" cfsqltype="CF_SQL_INTEGER" />
  • ORDER BY
  • qAllTags.name ASC
  • </cfquery>
  •  
  • <!--- Output the entry name. --->
  • <p>
  • #qEntry.name#
  • </p>
  •  
  • <!--- Get value list of tags. --->
  • <p>
  • #ValueList( qTag.name, ", " )#
  • </p>
  •  
  • </cfloop>
  •  
  • </cftimer>

For some 230 or something blog entries, the old-school database calls performed at about 1,200 ms to 1,400 ms with rare dips to 650 ms. The first query of queries method using one table performed at about 2,200 ms to 3,000 ms. The second query of queries method using two tables and join performed at about 3,500 ms to 4,200 ms.

So, in this case, the straight up database calls where faster. But, it's still a trade-off. Where do you want the processing to be taking place? If you have an application with a LOT of database activity, would it perform better to make less calls to the database and allow ColdFusion to do more of the processing?

Query of queries is so freakin' cool.



Reader Comments

Another place this comes in handy is joining queries from different database servers. For the data warehouse application I maintain, it's often useful to sanity-check yourself by running the same query on both servers and making sure you get the same results. Using a QofQ to join the two queries before dumping them out means that it becomes hella easy to spot where things might have gone pear-shaped.

Rick,

Agreed, most definately! One other place that I have found some very interesting uses it joining a CFDirectory query to a databae query (which I have had to do a few times). There are tons of uses for query of queries - they are really useful. I was merely testing some speed issues on a situation that I think comes up a good deal in websites.

But again, I can't stress how cool they are.