Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

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.