We want to transition from ACF to Lucee but are stuck with an issue. We are running Lucee 22.214.171.124 with MSSQL extension 6.2.2.jre8 on windows server 2016 against SQL Server 2016.
If we run a query without any parameters, it runs fast (low cpu and low reads on sql server).
select * from table where field='123456'
If we use parameters:
select * from table where field=<cfqueryparam cfssqltype="cf_sql_varchar" value="#sometext#">
the jdbc driver puts in a parameter of nvarchar(4000). It does this if we use cf_sql_char also, and specifying maxlength doesn’t help either.
So here is what sql server sees:
exec sp_executesql N'SELECT * from table where field=@P0 ',N'@P0 nvarchar(4000)',N'123456'
When that runs, it uses a ton of cpu and reads. If the same statement is changed to varchar(4000) or varchar(10) (something equal to the col width) or char(10) then it runs fast again.
So it seems there is a big issue with SQL server parameterized uni-code calls. Is there a way to not have Lucee/mssql jdbc always use nvarchar?
So I am not sure who is to blame, but either Lucee or mssql jdbc is changing the parameter from varchar to nvarchar. Anyone know which? It seems like a bug to me.
How can we get a newer mssql jdbc extension? 6.2.2 seems old, now that there is 6.4 and 7.0
thanks in advance