Home / Jetendo Lucee Extensions /

Jetendo LiveQuery Lucee Extension (Not Released Yet)

I called it Jetendo LiveQuery, because unlike the default cfquery that closes and hides the connection pool, connection, statement and resultset objects, this set of functions gives you new CFML function, data types and member functions for handling JDBC objects with a simpler API, but much more control of their lifetime just like you would have in Java or PHP.  However it works with Java access disabled in Lucee.

I believe JDBC should be more directly exposed to CFML developers especially when you need it.  JDBC is super efficient.    JDBC can handle querying more rows then would fit in the heap because it only needs enough memory for one row or column at a time.  With JDBC more exposed, the CFML application and socket can also be doing work in parallel maximizing performance instead of blocking and making extra copies like it does now.  PHP and Java can beat Coldfusion on query performance because they can easily do 2/3 times less memory accesses. 

We shouldn't have to deal with rewriting our application to meet the limitations of cfquery.  We should be able to build simpler data access that does only what is needed with fewer copies and it should be easier to make them run asynchronously in parallel so that our application can scale to use all of the CPUs.

When benchmarking various query approaches and working with JDBC directly, I learned that if you have a large resultset, reading and parsing all that data off the socket takes much more time then the query itself, which led me to design the API to encourage not only the query to be asynchronous but also the processing of it.   Lucee kind of already supports this with lazy queries, but there are several problems with that feature that I've been trying to fix on the open project in a JIRA ticket.

I'm trying to do more then duplicate that feature though.  I'm packaging a better way to do a variety of things in a new API that doesn't rely on CFQuery or any of its code.  It has a lot of potential for  noticeable improvements in both parallel and concurrent processing situations.

I also implemented this using Hikari Connection Pool, which is faster then what Lucee 5.3 uses and is supposed to be the fastest open source Java connection pool available. 

This extension is a work in progress currently, but the performance tests show it uses less memory and is much more stable under stress then the existing database access features of Lucee especially in the edge cases where you have a lot of data being returned or you have "select *" queries where you only need specific columns.   I also support getting data by column index instead of names since that is slightly faster.   

I've also been applying some of the fixes to Lucee core in my fork to make lazy better.

I'll post the extension here once I have finished implementing the configuration options for the connection pool and cleaning it up.   It is otherwise completed.

Here is what the API is looking like so far:

<cffunction name="index" localmode="modern" access="public">
// connection pool options - only needs to be run once on application start.
minimumQueryThreadCount:32, // minimum active connections to database
maximumQueryThreadCount:32, // maximum active connections to database.
futureThreadCount:8, // should match cpu cores
maxLifetime:25000000, // milliseconds until connection pool will stop an active connection. This should be less then the database timeout.
loginTimeout:9000, // milliseconds until database connection will throw. This should be less then the database timeout.
idleTimeout:600000 // milliseconds until no queries will close a connection.
// add the datasource property options too. default to the fastest ones that worked on mariadb 10.3
prepStmtCacheSize: 250,
prepStmtCacheSqlLimit: 2048,
cachePrepStmts: true, // seems to help when true
useServerPrepStmts: false, // its almost 30% slower with this set to true
useLocalSessionState: false, // dangerous for some apps to have this true
rewriteBatchedStatements: true,
cacheResultSetMetadata: false, // these features aren't useful it seems
cacheServerConfiguration: true, // no performance impact noticed
elideSetAutoCommits: true, // no performance impact noticed
maintainTimeStats: true, // no performance impact noticed
tcpKeepAlive: true, // no performance impact noticed
liveQueryPool = liveQueryConnectionPoolStart(ts);

// cacheable unique reference to a Java based LiveQueryImpl instance which can run multiple queries at once.
// You can create a new one when you want to have more then one group of queries processing separately.

processCom = createobject("component", "process");

sql = "select * from "&LQT(table)&" limit "&LQP(5)&", "&LQP(5); // abbreviated escaping of table and parameters.
liveQueryAsyncExecute(q, sql, this, "processRows", asyncId2);

sql = "select * from "&LQT(table)&" limit "&LQP(0)&", "&LQP(5); // abbreviated escaping of table and parameters.
liveQueryAsyncExecute(q, sql, this, "processRows", asyncId);

liveQueryWait(q, 20000);

// synchronously output all the results

// you can also do a synchronous query and still leverage custom HikariCP and the new memory efficient api.
sql = "select * from "&LQT(table); // abbreviated escaping of table and parameters.
rs=liveQueryExecute(q, sql);
while (rs.n()) { // abbreviated next
echo(rs.ci(1) & ", "); // abbreviated get value by columnIndex

<cffunction name="processRows" localmode="modern" access="public">
<cfargument name="rs" type="any" required="yes">
<cfargument name="asyncId" type="string" required="yes">
rs = arguments.rs;
savecontent variable="out"{
while (rs.n()) { // abbreviated next
echo(rs.ci(1) & ", "); // abbreviated get value by columnIndex
// cache the result somewhere

// the connection automatically closes after returning from this function unless configured to stay open.

Here is a quick summary of all the Java-based Lucee CFML functions in Jetendo LiveQuery

  • liveQueryConnectionPoolStart - Your own instance of a HikariCP threadpool!
  • LQStart - all the short ones are abbreviations
  • liveQueryConnectionPoolStop - shut it down, so you can make a new configuration.
  • LQStop
  • LiveQueryNew - A factory for executing queries - Helps with creating groups of asynchronous queries that must finish together.
  • LQ
  • LiveQueryExecute - run a query and block it can return a ResultSet
  • LQE
  • LiveQueryAsyncExecute - returns instantly after adding a query task to the connection pool executor.
  • LQAE
  • LiveQueryWait - Waits for thread termination, completion or timeout on all the asynchronous queries in this group.
  • LiveQueryParam - escape a parameter
  • LQP
  • LiveQueryTable - identify a table for sql analysis
  • LiveQueryTrusted - allow literal strings, numbers to be executed without escaping
  • LQT
  • LiveQueryResultNext - Get the next row in a resultSet loop
  • LQRN
  • LiveQueryRow - Get the current row as a struct
  • LQR
  • LiveQueryColumn - Get the column value by string name
  • LQC
  • LiveQueryColumnIndex - Get the column value by column index
  • LQCI
  • LiveQueryAsQuery - Return the resultSet as a regular CFML query object
  • LQQ

Top of Page