ColdFusion Query Of Queries Speed And Database Calls

Posted September 6, 2006 at 11:21 AM

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:

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

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

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page



Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

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 »
6,516 Comments
25,763 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.


Post Comment  |  Ask Ben

Recent Blog Comments
anang
Nov 22, 2009 at 1:56 AM
Learning ColdFusion 9: Using CFQuery In CFScript Can Enable SQL Injection Attacks
Why adobe would give you script equivalent of cfquery is beyond me. I love cfquery tag because it helps me wriite clean sql, and get away from the horrible jdbc queries If I wanted to write javali ... read »
anang
Nov 22, 2009 at 1:45 AM
Streaming Text Using ColdFusion's CFContent Tag And The Variable Attribute
The reason you would want to do this is to stream. Ack json/xml files to ria clients I used thus technique before because putting json in response stream causes debugging info to come thru As well a ... read »
komik
Nov 21, 2009 at 6:47 PM
Hal Helms - Real World Object Oriented Development, Sarasota - Day Five
@charlie griefer, Thank you.. ... read »
Jody Fitzpatrick
Nov 21, 2009 at 5:15 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jose Galdamez, Oh heh yeah I didn't paste the whole code. I should have defined the vars -- my bad. It's fixed thou. Thanks. ... read »
Unibands
Nov 21, 2009 at 4:49 PM
Styling The ColdFusion 8 WriteToBrowser CFImage Output
Great work yet again Ben! Whilst I didn't use this whole code, I copied some of your regex code for a similar problem with the lack of an alt attribute and unescaped ampersands in CFIMAGE for Railo 3 ... read »
Justice
Nov 21, 2009 at 1:13 PM
My First ColdFusion Builder Extension - Encrypting And Decrypting CFM / CFC Files
@Ben, Because I am pedantic, I just want to make sure that everyone knows there is absolutely no encryption going on. There is only encoding and obfuscation. The cfencode tool only obfuscates your C ... read »
Jose Galdamez
Nov 21, 2009 at 12:28 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jody I can't seem to get your code sample to work. If you are still having problems, try this code out and see if it gets you what you wanted. <!--- Comma delimited list with various duplicates ... read »
Esther Annies
Nov 21, 2009 at 11:03 AM
Groovy Operator Overloading Does Not Work In The ColdFusion Context
Hi Ben, Thanks for this informative post. Now I am reading ur old posts too ... read »