Skip to content

Using Lucee’s built in array returntype in CFQUERY – Performance benefits!

Published by Michael Horne on 11th October 2024

In our application, we perform a lot of CFQUERYs, just like most people using CFML as a backend that gets data from a database and then displays it.

In our case, we often do a lot of post-processing on the data being returned from a CFQUERY. Doing this to the actual query is, frankly, messy and a hard work and didn’t save us much development time. Therefore, we tend to convert the query result into an array. This is done using a module I wrote yonks ago called dataTypeConvert – the function is called queryToArray and does exactly what it says on the tin – it converts a CFQUERY query object into an array. It does this the “hard way” by looping over the rows and columns and creating an array of structs. If that sounds like a bad idea, or hard work for the server, then you’re probably right, but it’s “historical” and very useful. It means you can then loop over the array and change/add things to the structs within quite quickly.

I came across this Ben Nadel post in which he looked at the new “returntype=’array'” functionality in Lucee 5.3. I wondered if there was any performance benefit to using it – we throw data and dataTypeConvert calls around willy-nilly, but maybe there’s a better way.

I created a test function that queries a table called ‘prod_detail’ (which is a many-columned table) containing detailed products data.

<cffunction name="testArrayReturnType" returntype="string" access="public" hint="Hint">

  <cfset var local = {}>

  <cfset local.maxrows = 5000>

  <cfset local.output = "We are querying a table for #local.maxrows# records</p>">

  <cfset local.tick_start = getTickCount()>

  <cfquery datasource="#application.coyote.getSetting('dsCore')#" name="local.get" result="local.res" maxrows="#local.maxrows#">
    SELECT *
    FROM prod_detail
</cfquery>

  <cfset local.output = local.output & "<p>Original CFQUERY executed in #local.res.executionTime#ms">

  <cfset local.arr = application.coyote.getFactory("dataTypeConvert").queryToArray(local.get)>

  <cfset local.tick_end = getTickCount()>
  <cfset local.duration = local.tick_end - local.tick_start>

  <cfset local.output = local.output & " and the whole thing took <strong>#local.duration#ms</strong></p>">

  <cfset local.tick_start = getTickCount()>

  <cfquery datasource="#application.coyote.getSetting('dsCore')#" name="local.get" result="local.res" returntype="array" maxrows="#local.maxrows#">
    SELECT *
    FROM prod_detail
  </cfquery>

  <cfset local.output = local.output & "<p>CFQUERY with array returntype executed in <strong>#local.res.executionTime#ms</strong></p>">
  <cfreturn local.output>
</cffunction>

As you can see, what it does is to run the query twice (we’re using MySQL, so no caching of results or anything – we tested that!). In the first instance, it uses the dataTypeConvert function queryToArray to get an array of structs. In the second instance, it uses returntype=”array” to do the conversion within Lucee (and I assume within Java).

With the maxrows set to 5000, the following output was produced:

We are querying a table for 5000 records

Original CFQUERY executed in 112ms and the whole thing took 5362ms

CFQUERY with array returntype executed in 141ms

As you can see, there is a MASSIVE difference. We tried it the other way around, too, and got the same result.

So, if you’re doing conversion of queries because you want to manipulate the data, and you haven’t looked at this functionality in Lucee yet, give it a go! 🙂

Published incfml