Sorting out your CFML queries

No matter what you do with CFML at some point the chances are you are going to be doing some SQL and utilising the powerful <CFQUERY> functionality to retrieve results from some remote database.

The result returned back from this tag is what is known as a Query object.  This is bascially just a series of rows and columns that represents the data your query returned.   CFML has a number of other tags that can also return a Query object for example <CFDIRECTORY>.  Irrespective of the source, you can then combine these objects together and perform operations on them.

What if you wanted to sort this Query object?

Of course the easiest option is to put the sorting directive within your original SQL statement, using some sort of ORDER BY switch depending on the database engine you are hitting.  However this may not be the best solution, particularly if the query took a long time to execute in the first place.  So if you are providing a UI that is letting the user sort on a given field, then rerunning the query each time just because of sort isn't going to win you any favours from the database adminstrator.

An alternative to this is the infamous Query of Queries functionality.  This is where CFML has provided you with a complete in-memory SQL language to manipulate one or more Query objects.  This is a very powerful feature set and allows you to do a whole manner of cool stuff, including combining many Query objects into one.

However there is a downside to using Query of Queries for sorting.  It uses memory.  Imagine you have a query that has a 1,000 rows in it, and you perform a sort with it.  Now you will have effectively 2 copies of this result set.  One for the original query and another for your new query.  Of course you can copy over the last one, but the underlying JVM still has to allocate and then dispose of that memory, even though you think you done it all in one step.

If a simple sort is all you are after, then Open BlueDragon offers an alternative to this technique.  The QuerySort() function takes in a query object and performs a sort on that dataset, creating no new data or any copies.  It sorts the original Query object for you.  This has proved to be a much more efficient way of managing sorting particularly in a high-request environment.  Let us look at a quick example:

<cfquery name="myqry" datasource="mydatasource">   
  select name, address, age, address from person 
</cfquery>

<cfset QuerySort( myqry, "name", "text", "asc" )>

This is a highly memory efficient way of managing the order of your queries.  Sorting can be done on any column, and on either TEXT|NUMERIC types going in whatever ASC|DESC direction you wish.

This function is in the core distribution of Open BlueDragon and can be used with any Query object irrespective of its origins.