Query each slow (Lucee 6.2.)

dev support
1

I upgraded my local stack to lucee 6.2.2 / jdk 21 / tomcat 11.

I have a small script, uploading a csv file (6 columns), parse each data field (e.g. validate) and then i collect the rows in an array. If the array length is 100, I create one “INSERT INTO” sql statement with 100 rows.
The file has 363 rows, really nothing special and this works within less seconds.

After the upgrade, the script ran in a timeout and the Query each-function is terrible slow:

insert_100
insert_1001202×218 22.8 KB

The Lucee web admin also shows a lot of implicit variable access - not sure if this is related:

implizit_variable
implizit_variable1222×308 34 KB

I switched from 100 data rows to 10 rows, makes it much faster:

insert_10
insert_101368×176 24.1 KB

This feels like a bug?

2

Here’s a small test case that reproduces the bug.
To get it running, create the test table (first comment) and change the datasource (line 14). I hope this helps.

query each slow.cfm (2.4 KB)

3

One more update: Lucee 6.1.1.118 on old hardware, same csv file as in my first posting:

CleanShot 2025-07-04 at 15.17.17@2x
CleanShot 2025-07-04 at 15.17.17@2x1384×1106 187 KB

4

Does it happen with CF loop too?

I’m on my phone at the beach, you’re using queryExecute based on the helperBase.cfc, plain old cfquery should be fine tho

Obviously this seems to be a regression

1 Like
5

are you using the same db driver version?

6

I tested the latest 5 mysql drivers, same results.

@Zackster I am unsure, what I should test. So when you are back from vacation, please give me some more details.

I tried several versions (lucee, tomcat) and this version is extremly faster:

Lucee: 6.1.1.118
Tomcat: 9.0.105
Java: 21.0.7

With tomat 9 and luce 6.2.1.122 it is slow. I hope this helps.

7

Ok, I’m back

Firstly, using Query() is the slowest way to do queries in Lucee

This introduced some extra overhead and was missing some variable scoping, plus it’s revalidating all the params each time a param is added
https://luceeserver.atlassian.net/browse/LDEV-5050

I’m tracking this with this ticket

https://luceeserver.atlassian.net/browse/LDEV-5689

Generally speaking Query() should be avoided if you care about performance

    qNew = new Query();
    qNew.setSQL( stringBufferOutput.ToString());
    qNew.setDatasource( datasource);

    for ( stParam in aParams) {
        qNew.addParam( argumentCollection = stParam);
    }
    qNew.execute().getResult();

438ms 6.1.1.118
493ms - 6.2.2.68
484ms - 6.2.2.69 (with some simple var scoping, better fix to come)

Compared to good old cfquery…

cfquery(sql=stringBufferOutput.ToString(), datasource=datasource, params=aParams );

23ms

This running tests vis script runner without tomcat, using java 21.0.6.7

8

Thank you very much for your reply. Is there a chance you will merge the logic in the backend or do I have to rewrite the code? We have currently 888 “new Query” in the code…
Are there any other deprecated functions I should know about? I just know this page: Deprecated Tags & Functions :: Lucee Documentation

9

We will be addressing that perf regression, but it will never be as fast as plain old cfquery, especially when working with queries with a lot params, so refactoring is defiantly going to potentially bring performance benefits, depending on your usage

Adobe did deprecate all the new Query() stuff we haven’t as of yet, it’s just slower, but some people prefer the more expressive approach

As for other things, nothing springs to mind, using stringbuilder like that, especially with newer versions of java, might not be worth the effort, but that’s something to benchmark yourself

10

6.2.2.72-SNAPSHOT has the additional performance tweak

with a test case with 500 params, it now takes 232ms, rather than 345ms

The latest Lucee snapshots aren’t currently available via the admin due to some maven problems, you can just drop this into /deploy to test https://cdn.lucee.org/6.2.2.72-SNAPSHOT.lco

11

Or QueryExecute()?

12

As per the documentation, cfquery is the fastest

13

Ah, I see. Well, glad to see that QueryExecute() has near-identical performance because cfscript is so much easier to read, especially when the <cfquery> has tons of ugly <cfqueryparam> tags, which every secure query should, of course.

Also, the QueryExecute() code example in that doc page is more verbose than it needs to be, and oddly uses positional parameter binding instead of named parameters which are much easier to read.

1 Like
14

Wanna dive in and improve the example, or add another?