queryToArrayOfStructs() is bad for performance

[JamoCA]

I was troubleshooting a qb query that was taking ~2 minutes to return results using the default settings whereas executing the SQL directly was much, mucher faster. I changed returnFormat to "query" and it only took ~26ms.

I realized this performance issue is directly due to queryToArrayOfStructs in the QueryUtils CFC: https://github.com/coldbox-modules/qb/blob/bf33c1a706ec8520be7dbcce79a7aaaa628c87c1/models/Query/QueryUtils.cfc#L228

As a post-QB workaround (using CF2021), I converted the query to an array of unordered structs the array return type.

function queryToArrayOfUnorderedStructs(required query q) hint="Use native QoQ function to generate array; much faster" {
    return queryexecute("SELECT * FROM [arguments].q", {}, {"dbtype":"query", "returnType":"array"});
}

Since support for CF2016 has been dropped, perhaps this function could be modernized to use the QoQ returnType apprach (if supported) otherwise fallback to slower, manual generation. The only downside to QofQ in ACF is that the returned struct isn't ordered, but I don't think it's is a showstopper. (If it is, perhaps an unordered option could be added as an option for performance reasons.)

[elpete]

Good news — qb 9 has you covered: https://github.com/coldbox-modules/qb/commit/3e7529de85904b158a3916b7c39623e97dc3ac50

[JamoCA]

ok... Thanks!

When I searched, I was thinking that the supportsNativeReturnType logic would be integrated into the existing, public queryToArrayOfStructs function rather than separately IF/THENed within the queryBuilder CFC. Upon further review, this utility function is only used once so it won't be a problem. But if it were used in more than one place, it'd be best to add the native check/shortcut to the queryToArrayOfStructs function. (I searched the v9 release notes and didn't think that it had been modified since there was no mention of enhancement.)