Query of Query vs QueryFilter

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.

{"QueryofQuery":15892,"QueryFilter":4803,"records":2}

When I swap out the sample 2 record query for one with a moderate 215 records, the results completely swap

{"QueryofQuery":57184,"QueryFilter":209920,"records":215}

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.

Lucee: Lucee 5.2.7.44-SNAPSHOT
OS: Windows 10 (10.0) 64bit
Java: 1.8.0_144 (Oracle Corporation) 64bit

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

1 Like

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.

1 Like

Can you share the performance stats?

1 Like

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.

Thanks!

1 Like

Did you follow my suggestion about checking Implicit variable Access ?

1 Like

Hi Zac, I don’t have debugging turned on for dev or production so this shouldn’t be artificially slowing it down in either spot.

Thanks!

No, I’m talking about using debugging to identify performance problems. Debugging in Lucee is light weight. Try it, you’ll see what I mean

I’ve updated the QoQ sucks with some examples to demonstrate what I’m try to explain.

Performance does vary a bit between runs, but the scoped closure is always the fastest

1 Like

That’s a great little addition to the docs :rocket:

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**);

Can you please give an example of a properly scoped closure? Not sure what that means…

For example referencing closure arguments as arguments.value instead of just value

Try the debugging option, it will tell you about any problems