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 »
11,246 Comments
75,418 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
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Muhammadibn
May 24, 2013 at 9:11 AM
Preventing Links In Standalone iPhone Applications From Opening In Mobile Safari
@Brandon, Hi, No, I haven't been able to do that. I have just kept it as it is. ... read »
Brandon
May 23, 2013 at 9:52 PM
Preventing Links In Standalone iPhone Applications From Opening In Mobile Safari
@Muhmmadibn Did you figure out a solution to launching PDFs? I am running into the same issues myself. There is no way to close the PDF or go back once you launch it. Thanks in advance! ... read »
Dr Amadi
May 23, 2013 at 6:06 PM
The Girl Who Broke My Heart, And Made Me A Better Person
Good day,ladies and gentle men, my name is Dr AMADI the great spell caster in Africa, i have help so many people for different kind of problems,who say there is no solution to problems on earth, that ... read »
Ben Nadel
May 23, 2013 at 4:26 PM
ColdFusion QueryAppend( qOne, qTwo )
@Heather, Glad people are still getting value out of this! ... read »
Ben Nadel
May 23, 2013 at 3:49 PM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@WebManWalking, I meant the code at the bottom (not the video). I did try to experiment with an intermediary variable, like: value = users.id[ i ]; arrayContains( userIDs, value ); ... but t ... read »
WebManWalking
May 23, 2013 at 11:06 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, Are you talking about As Number: YES As String: YES As Java: YES? If so, that's with 3 different ways of referencing the constant 1, not users.id[1]. Query object references(*) are what seem ... read »
Ben Nadel
May 23, 2013 at 9:55 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dan, According to the CF Admin, I'm running Java "1.6.0_45". As far as the DB column, in the database it's an INT. I'll see if I can dig into what CF sees it as. @WebManWalking, But h ... read »
WebManWalking
May 23, 2013 at 9:49 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, I think the problem is that we're used to loose typing in ColdFusion, like JavaScript. If a value is a number but it's needed in an expression to be a string, noooo problem. I've encountered ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools