ColdFusion Query Of Queries Speed And Database Calls

Posted September 6, 2006 at 11:21 AM by Ben Nadel

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

Rick O
Sep 6, 2006 at 12:16 PM // reply »
153 Comments
277 Points

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.


Ben Nadel
Sep 6, 2006 at 12:20 PM // reply »
10,640 Comments
69,065 Points

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.


Austin Houser
May 3, 2011 at 8:50 PM // reply »
1 Comments
12 Points

Ben,

Great post! I was just about to build out my own test script, thanks for taking the time to share your findings.

My best,
Austin


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 »