ColdFusion Query Of Queries Speed And Database Calls
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.
Want to use code from this post? Check out the license.
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.
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