Query of Query support in Lucee does not perform well. The native Java implementation of QoQ only supports the most basic of SQL. All complex queries throw an exception and fall back to a second attempt which runs the select against an embedded HyperSQL database. This has a few issues:
The HSQLDB implementation is single threaded due to a synchronized block which makes QoQ untenable under any sort of load
There is a base overhead of the JDBC connection and parameter handling that makes any page with a large number of of QofQs to slow down significantly
HSQLDBs parser is not very good and throws exceptions for many valid SQL statements such as count(1)
Improve the native Java implementation of QofQ to support
group by clause
Allow aggregates to reference nested operations including scalar functions
ceiling( max( floor( yearsEmployed ) )+count(1) )
faster distinct support
Fix buginess in unions were results aren't distinct by default (union distinct)
Improve count() to support
count( all col )
count( 1 )
count( * )
count( 'literal' )
count( distinct col )
count( distinct scalarFunc( col ) )
Fix bugs in SQL parser that incorrectly requires only a single space between multi-word clauses
is not null
Remove single-threaded limitations
Performance tune speed of queries
I am submitting a pull request that includes all of this. I have done rigorous testing and added a new suite of tests for all the functionality. I have also done side-by-side performance tests with Adobe CF and the new implementation is faster than Lucee's old implementation in every single test and is faster than Adobe CF's implementation in most of the tests. All code in the pull request is fully commented.
I have also added support for the following system props/env vars
lucee.qoq.hsqldb.disable=true – Throw exception if native QoQ logic fails
lucee.qoq.hsqldb.debug=true – Log message to WEB context's datasource log any time a QofQ "falls back" to HyperSQL. This could include just bad SQL syntax.
I would love to see the improvements get into Lucee. We have a very large application as well that uses QoQ to paginate cached data, so anything to boost performance has a huge payoff for us.
I was playing with a threaded test against Lucee with this patch, given QoQ was previously all synchronised
attached is thread.cfm, sometimes the intermediatory qoq sum values are all over the shop
behaviour is rather different with the lock around the QoQ in the threaded function
you was right with your concern about concurrentmodifucationException, there was actually one place causing problems.
Brad has pointed out the following
This pull is for ticket
It should also fix tickets
Please give all this tickets a try and if necessary create test cases for them. Please report in this tickets if hey got solved by this change.