MSSQL parameterized query performance issue

dev support
1

We want to transition from ACF to Lucee but are stuck with an issue. We are running Lucee 5.2.9.31 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).

Say:

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

2

I now noticed the switch for sending unicode on the datasource setup and it now sends it as varchar(8000) which fixes the issue. I didn’t see it until I went back in and re-read it. There should probably be a really big warning there as setting it to unicode pretty much killed our server.

3

Yeah that option sucks. We leave it off and we deal with unicode on a case by case basis. (i.e. you’re going to have to deal with nvarchar and ntext fields specifically)

4

Ive been working on the same issue on an older railo version. The answer is to use the JDBC driver flag
sendStringParametersAsUnicode=false, which is a little misleading basically you can send parameterized queries in mixed mode. So once this param is set you should be able to use queries with cf_sql_nvarchar along side cf_sql_varchar

*note i also had to switch to the MS sql driver