The Lucee team have just put out a short screencast warning us that "Query-of-Query Sucks?".

Most CFML developers will be familiar with QoQ which allows you to write SQL queries against query recordsets which have already been retrieved from the database.

This seems really handy when you need to perform further filtering, sorting or aggregation on the data while avoiding another round trip to the database. The SQL syntax (albeit a limited subset) is familiar and seems appropriate for these kinds of tasks.

But as Micha reminds us in the screencast, QoQ is notoriously slow, and there are other ways of doing the same kinds of post-processing on queried data sets.

I've made quite extensive use of QoQ over the years so I thought I'd look at a few examples of how they could be replaced with simpler and faster alternatives.

SELECT MAX( column )

I had a query containing a set of rates which was being used for a tabular display, but I also needed to pull out the highest rate for another purpose within the same request. I passed the loaded query into a function which did that using QoQ:

numeric function highestRate( required query rates ){
 return queryExecute( "SELECT MAX( rate ) AS result FROM rates", {}, { dbtype: "query" } ).result;
}

There's no need for SQL to get the maximum value though. That can easily be done by extracting the rate column values as an array and using a simple CFML function - arrayMax() - on that. Using member functions that's as simple as:

numeric function highestRate( required query rates ){
 return rates.columnData( "rate" ).max();
}

Type ahead suggestions

Another common feature I've generally implemented using QoQ is "suggest as you type" search. Javascript on the client passes the typed input via AJAX to the back-end which returns a JSON set of matching items.

Typically the complete set of data is queried from the database and cached in memory. Then QoQ is used to pull out the matching items.

private query function matchingItems( data, input ){
 var params = { input: "%#LCase( input )#%" };
 var sql = "
  SELECT
   ID
   ,name
  FROM
   data
  WHERE
   LOWER( name ) LIKE :input 
  ORDER BY
   name
 ";
  return queryExecute( sql, params, { dbtype: "query" } );
}

These days filtering and other types of operation can be performed on data structures fairly easily using "higher order" functions (those which can take another function as an argument). In this case I'm going to use queryFilter():

private query function matchingItems( data, input ){
 // define my filtering rule as a function
 var filterRule = function( row ){
   return row.name.findNoCase( input );
 };
 return data.filter( filterRule, true );
}

As well as the speed increase in avoiding QoQ, Lucee offers an additional potential boost in the form of the second parallel boolean argument which can be passed to queryFilter(). This tells Lucee to execute the filter rule function in parallel if the hardware supports multi-threading.

Update 5 September 2018 Although parallel processing seems to work well with relatively small data sets, I've found that with larger sets (multi-thousand) it can cause the server to lock up, even when the maximum number of threads executed is set to a low number such as 5 (the default is 20). I would therefore use it with caution.

Simple re-ordering

Finally, I sometimes find I need a data set to be in a different order than that specified in the original SQL "ORDER BY" clause. Instead of having to query the same data again in a different order, I would use QoQ to manipulate the existing set in memory.

// posts are ordered by datePublished ascending: change to descending
query function postsByDatePublishedDescending( required query posts ){
 var sql = "SELECT * FROM posts ORDER BY datePublished DESC";
 return queryExecute( sql, {}, { dbtype: "query" } );
}

As a higher order function in CFML, querySort() can be passed a closure/function to perform the sort. But Lucee also gives you the option of simply passing in the column name(s) to order the result by, instead of the closure. A further argument lets you specify the direction: ascending or descending.

query function postsByDatePublishedDescending( required query posts ){
 posts.sort( "datePublished", "desc" );
 return posts;
}

Simpler and faster.