SQL Server queries are running slower in Lucee than in ACF

dev support
1

We are in the process of migrating from ACF to Lucee. The stack information is below. There’s a particular query which does a search runs significantly slower than ACF. It’s 2 seconds in ACF and it’s 11.6 seconds in Lucee. The same query runs instantly in Sql server directly. Has anyone faced this issue and rectified it.

SELECT DISTINCT *
FROM vw_sample vs
WHERE 1=1 AND vs.reference LIKE ‘%TEST13JUL2023%’
ORDER BY ID DESC

Don’t forget to tell us about your stack!

OS: Windows Server 2022 (10.0) 64bit
Java Version: 11.0.17 (Eclipse Adoptium) 64bit
Tomcat Version: Apache Tomcat/9.0.68
Lucee Version: Lucee 5.4.1.8

2

try sendStringParametersAsUnicode=false

with that set varchar indexes aren’t used for strings

image
image1916×380 151 KB

connectionString: "jdbc:sqlserver://localhost:1433;DATABASENAME=lucee;sendStringParametersAsUnicode=false;trustServerCertificate=true;SelectMethod=direct",

3 Likes
3

What version of SQL server?
Do other queries run fine? Do other similar queries run fine?

I have not seen your issue, but would recommend the following troubleshooting steps to narrow things down:

  1. Determine where the time is going:
    Run Profiler tool on MSSQL to check if the query is indeed executing quickly or not.
    Place cftimer before and after the cfquery to confirm time for the call.
    Now you should know if the time is magically disappearing somewhere in the driver or SQL is for some reason taking longer when called by Lucee.

  2. Experiment with the SQL statement, does the time change based on the nature of the query?
    Meaning start simple and build up:
    Select [myIdColumn] from vw_sample where 1=0
    Select top 10 [myIdColumn] from vw_sample
    Select top 10 * from vw_sample
    If those run fast keep adding conditionals one by one until you get back to your original query and see at which operation it slows down.
    If those first simple selects run slow as well, then it seems like it would most likely be a driver/lucee problem, although i would grab the sql from vw_sample and place it directly into cfquery tag to confirm its not something weird with the view sql.

By this point at least you should have a better idea on where to focus; if its drivers, play with admin settings (maybe delete and recreate your lucee DB connection, check all settings, etc) possibly upgrade driver version if available, etc.

4

nah, my solution is the answer

1 Like
5

Thank you so much @Zackster. That solved it. :slight_smile: We are closer to the release.

2 Likes
6

We have fusion reactor which helped us pin point to the sql. The solution suggested by @Zackster has solved it.

2 Likes
7

Great, i’m always in favor of 1-click solutions! :wink:

Anyone know anything more about that setting “Send String Parameters as Unicode”?
Does it really only apply to MSSQL 2005 and below? Are there very specific conditions where it makes sense to change it to false?

Just curious as I’ve never touched that setting before nor noticed any lucee performance issues with text wildcard searches.

8

It sounds like SQL Server has a serious performance problem with Unicode as they could check the data and use the fast path themselves.

9

I found different versions of drivers have difference performance implications also.

try a few different versions. My 2c

2 Likes