Query of Query Sucks

Query of Query Sucks

This document explains why Query of Query is not the best approach and provides simple example about QoQ.

The main reason you would not want to use query of query is that it is very slow.

Update, the performance of QoQ has been dramatically improved in 5.3.8!


Example :

	q = QueryNew("name, description");
	loop times=3 {
			var fd = getFunctionData(arguments.f);
			var r =QueryAddRow(q);
			QuerySetCell(q,"name", fd.name, r);
			QuerySetCell(q,"description", fd.description, r);
	    label="demo data set size");
	s = "the";
<cftimer type="outline" label="Query of Query">
	<cfquery dbtype="query" name="q1">
		select 	name, description
		from 	q
		where 	description like <cfqueryparam value='%#s#%' cfsqltype="varchar">
<cfdump var=#q1.recordcount#>
<cftimer type="outline" label="query.filter() with scoped variables">
		q2 = q.filter(function(row){
			return (arguments.row.description contains s); // works even better with variables.s in lucee 5.3.3
<cfdump var=#q2.recordcount#>
<cftimer type="outline" label="query.filter() with unscoped variables">
		q3 = q.filter(function(row){
			return (row.description contains s);
<cfdump var=#q3.recordcount#>

In this example have a QOQ with persons table.

// index.cfm
directory sort="name" action="list" directory=getDirectoryFromPath(getCurrentTemplatePath()) filter="example.cfm" name="dir";
loop query=dir {
	echo('<a href="#dir.name#">#dir.name#</a><br>');
// example.cfm
// Query of Query
loop times=max {
	query dbtype="query" name="qoq" {echo("
		select * from persons
		where lastname='Lebowski'
		and firstname='Bunny'
		order by lastname
dump("Query of Query Execution Time:"&(getTickCount("micro")-start));
// Query Filter/Sort
loop times=max {
	qf=queryFilter(persons,function (row,cr,qry) {return row.firstname=='Bunny' && row.lastname=='Lebowski';});
dump("Query Filter/Sort Execution Time:"&(getTickCount("micro")-start));

In this example have two different methods of queries.

1) First one is QOQ. Here QoQ from the persons table. It is executed a thousand times due to the looping required by QoQ.

2) The second one is calling the function query filter. Query filter filters out the same row the same way the QOQ does.

3) Execute it in the browser and we get two results(Query of query execution time and Query filter/sort execution time). Query filter executes twice as fast, at least, as query of query. Because QOQ loops over and over again, it is slower. If you can avoid QOQ and use the Query filter/sort, your code will execute much faster.


You can see these details in the video here:

Query of Query Sucks

See also