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 ( [LDEV-2097] - Lucee ) 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.