Query of Query performance is very bad and single threaded for complex SQL
Description
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
having clause
aggregate functions
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 null
is not null
not in
not like
order by
group by
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.
