I’ve been operating under the assumption that QueryOfQuery is extremely slow and that a far faster alternative is to use QueryFilter. I’m basing this assumption off of the official Lucee developer guide about this very topic
However I noticed that when I used QueryFilter in my code it was painfully slow. On a hunch I tried using QoQ instead, and it ran in close to one third of the time. Since this was the exact opposite of what the Lucee developer guide said, I went back to that guide decided to run the test found there on my own.
Running the test as is from the guide yields the same results, QoQ performs about 3 times slower than query filter for a query of 2 records.
Below are the results from that test, when run locally.
Now the QueryFilter is performing about 3 times slower than QueryFilter.
Has anyone else experienced this? I don’t mind that query of query is faster, in fact I prefer it, but seems strange there’s an entire article in the Lucee docs specifically stating the opposite, when that seems to be true only for extremely tiny queries.
I’d always recommend trying the latest snapshot, 5.2.7.44 is pretty old, as is your java version
if you enable debugging with Implicit variable Access under Settings - Debugging, your code might be slow due to variable scope look ups…
it’s hard to say without seeing your code, but this is a great way to identify performance problems due to missing scoping. I recently used this technique to improve massively the performance of the modern debug template for long debug logs from big complex pages
Thanks for the suggestions Zac. I never realized how big of a performance hit scope look ups could cause. properly scoping the row variable within the queryFilter closure made a huge difference. Additionally, trying this on the newer version of Lucee seemed to help a ton as well.
Knowing we have a few spots where we use QoQ (sparingly), I decided to test this out to see if changing out these few spots with QueryFilter would be faster.
Our page happens to have 2 QofQ based off the original query to pre-parse out some of the data.
Total records in original query – 230
1st Query - 10 records
QoQ1 - avg 420 ms
QF1 - avg 1310ms
2nd Query - 220 records
QoQ2 - avg 1950 ms
QF2 - avg 2640 ms
At least for our data and real world page scenario the Query of Query was faster 98% of the time.
We do try to not use QoQ unless it really needed for the code just since a cfloop and building a query is truly faster in comparison when dealing with small record sets.
For curiosity, I tried flipping our QoQ1 to just a straight query the limited 10 records – avg count was around 3000 so MUCH slower than the first QoQ.
Another thought worth mentioning is if you are looping over the results and performing some operation, setting parallel execution could make a dramatic improvement compared to QoQ > Loop > Code Block:
query
.filter( (row) => { return my match });
.each( (row) => { my code block }, **true**);