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.

Activity

Show:
Dan Switzer, II
September 11, 2020, 8:39 PM

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.

Zac Spitzer
September 11, 2020, 9:10 PM

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

Michael Offner
September 21, 2020, 10:23 AM

Michael Offner
September 21, 2020, 12:22 PM

you was right with your concern about concurrentmodifucationException, there was actually one place causing problems.

Michael Offner
September 21, 2020, 12:24 PM

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.

Assignee

Pothys - MitrahSoft

Reporter

Brad Wood

Sprint

5.3.8 Sprint 3

Fix versions

Priority

Major
Configure