Cfquery is running on really efficient Java code


#1

I tried really hard to make a faster version of CFQuery today by modifying the Lucee java, and I had several other ideas, but I found they’ve got it probably the fastest it can be after testing my changes. I just reverted to how it was for the most part.

Every little change I tried just made it slightly slower. I wouldn’t have thought a column array loop would be faster then a hashMap. I think the only way to speed up Lucee data access is to change the driver or connection pool third party code to something else, because the Lucee side is really nice.

With cfquery lazy=“true”, it is really at the peak performance of what JDBC can do. You probably don’t want to use lazy if you need to cache the query, but otherwise it gives an exciting performance improvement. For those that don’t know yet, lazy=“true” can make “select * from” queries faster more easily if you aren’t reading all the values and you are using cfloop query instead of a for…in loop. Most of the cost of queries is the time spent passing the data through the socket.

Great job finding ways to get this faster over the years!

There is only one flaw in cfquery I can find.

While lazy query gives you closer to the raw performance of JDBC ResultSet, it is extremely dangerous to use lazy=“true” on a longer batch processing request because the full resultset stays in heap for the entire request and is only released by Lucee when the PageContextImpl release function is fired at the end of the request. I reported this 5 years ago on the Railo forum, and this problem is still there today.

Fortunately, my new Java skills allowed me to implement QueryClose(queryName); myself, and I have posted a bug report and pull request here ( https://luceeserver.atlassian.net/browse/LDEV-2097 ) to encourage this function becomes part of Lucee core. This will make it easier to use this feature on the whole application as long as we make the habit of closing these lazy resultsets. Lucee is so close to perfect on data access performance now.

I also don’t like queries returning a literal null value because the difference between empty string and null is not important to my application and it creates extra work to deal with null as a concept. I modified my fork of Lucee core to return empty string even when I have null support on. Little things like this save me a lot of hassle. It became even more important to do this when using lazy resultsets because it returns a simple array of structs instead of a query object. This simple object with null values causes Lucee to say the column doesn’t exist even when it is there when you dump it. By forcing empty string in the sql casting java classes, I can output every column without any validation code, while still enjoying full null support in other situations.

A Coldfusion application migrating to the optimal Lucee style of CFML is many times faster especially after you work out the concurrency and variables scope bugs hidden deep in Coldfusion apps and use localmode modern, lazy queries, and disable scope cascading features. Lucee also gives you tools to measure this in the debugging log, and it is open source, so you can customize it. Lucee is now part of the application to me instead of a third party tool.


#2

@brucekirkpatrick thank you for the feedback. It’s very much appreciated.


#3

FYI we improved null handling a lot with the latest 5.3 snapshots. It was slower than with Lucee 4.5 because we newly support to set full null support yes/no in the application.cfc. Now it is slightly faster than Lucee 4.5.

We actually spend a lot of time to improve performance of Query within the limitation CFML gives us.

One think i was considering doing is to detect how a query is used (on compiler level) and use Lazy queries automatically if possible.
We can detect if a query “leaves” the local scope or is passed (maybe) to the outside a function and how it is used. in case we are a 100% sure it is safe to use lazy queries we then could do it.

This is the way how we improve today performance, detecting patterns in code and the create specialized bytecode for certain pattern.
Lucee can for example detect certain pattern with cfloop and make the bytecode specific to that.