[FRS-421] Performance issues using query of queries on a Lucee application server


Introduction


This technote will provide you with more information and a workaround for a potential performance issue when using query of queries in Lucee 4.5.2 and above.

More Details about the Issue


From Lucee 4.5.2 to the latest available version (4,5,5 at this point in time) there is a possible performance issue when using a query of queries. One case we know that affects this is using a COUNT on the query result set. We see that for every record Lucee moves into a local DB to process a JDBC insert query is tracked in FR.

Here is an example cfm snippet that will cause this issue:

 <cfquery name="getRows" datasource="local">
      select ID from Table1 LIMIT 1600000
</cfquery>

<cfquery name="count" dbtype="query">
      SELECT COUNT(ID)
      FROM getRows
</cfquery>

<cfdump var="#count#"> 

Workaround


You can add the following -D property to your server configuration file to stop tracking the lucee insert requests

-Dfr.jdbcconnectionexcludeclasses=org.hsqldb.*

In order to do this, please follow the steps below.

  1. Stop your Application Server.
  2. Locate the JVM arguments file and backup that file.
  3. Open the JVM arguments file with a text editor of your preference and add the above argument.
  4. Save the changes.
  5. Start the Application server.
  6. If for any reason you experience any issues please restore the backup of the JVM file and restart the Application server.

Please note that the development team is currently working on a fix which will be available soon. If you have further questions or issues with FusionReactor, please feel free to contact the Support Team on the following email: support@fusion-reactor.com.

Issue Details

Type: Technote
Issue Number: FRS-421
Components: JDBC
Environment:
Resolution: Fixed
Last Updated: Today 3:03 PM
Affects Version: 6.2.7
Fixed Version: 7.0.0
Server:
Platform:
Related Issues: