While trying to troubleshoot a performance issue with one of my queries, I discovered a behavior which certainly looks like a bug to me. When I execute the following code in Lucee

<cfquery name="text">
	select <cfqueryparam cfsqltype="CF_SQL_CHAR" value="2" />
</cfquery>

And then I monitored the actual SQL that SQL Server executes, I'm seeing the cached plan end up as:

exec sp_executesql N'select @P0',N'@P0 nvarchar(4000)',N'2'

Notice that instead of

2

being cast as

char(1)

it's cast as an

nvarchar(4000)

. Any of the string values are behaving the same way. Shouldn't the executed SQL look like this instead:

exec sp_executesql N'select @P0',N'@P0 char(1)','2'

Did you try adding maxlength="1" to the cfqueryparam to limit the length?

Could it be char is fixed length ?

I'm running Lucee 5.3.10.97 with the MS SQL JDBC driver v9.5.0 (which we built from source)

That's interesting that maxlength is ignored. I'm not surprised that it isn't measuring the length of your value before being executed without it, and just defaulting to being permissive to large blocks of text.

This is happening on every query with with a string datatype. Since it's ignore the datatype, it's causing SQL Server to implicit convert the values on-the-fly, which is leading to some query plan issues.

there is an old performance topic on dev lucee that may be related: https://dev.lucee.org/t/mssql-parameterized-query-performance-issue/4649

@steveduke That looks like it might be the issue. Making changes now to see if that fixes things.

let me dig in and have a look

@zackster It also seems as if there's no way to actually declare a query param that will end up being generated as a

char

datatype, even though the JDBC spec allows it. Is that correct?

i had a bit of a play with it and looked thru the source code, I don't see any lucee code which is doing and explict type conversion. it could be something inside the jdbc driver, did you try another jdbc driver like say mysql?

@dswitzer Dan, did you ever come to a conclusion on this issue, of your cfqueryparam ending up an nvarchar(4000)?

@carehart Yes, so enabling

sendStringParametersAsUnicode

does always send all params as internationalized datatypes (i.e. "n"). This is per the JDBC spec. However, from what I can tell there, the MS JDBC driver will always translate "string" parameters to varchar or nvarchar datatypes: https://github.com/microsoft/mssql-jdbc/blob/9cbaf1754194c4a6391e192f12fc59df7ad3ee03/src/main/java/com/microsoft/sqlserver/jdbc/Parameter.java#L717 From what I can determine, there does not appear to be any performance issue with comparing varchar parameters to columns stored as char datatypes. The main difference is internally in how SQL Server stores the value. SQL Server shows no signs of any implicit datatype conversions comparing char-to-varchar/nchar-to-nvarchar. It does seem like Lucee could specify sending the size of the varchar/nvarchar datatype, but looking at the ACF docs, it does not actually state that

maxLength

does anything with the database, it just validates before the string is sent to the DB. So the take away for me is: 1. The docs for

sendStringParametersAsUnicode

setting (i.e. "Send String Parameters as Unicode") should be more clear that enabling this will always send all parameters as internationalized and will ignore the datatype you specify in the cfqueryparam. My guess is this probably effects a lot of people and they are unaware. This can definitely cause performance issues if your database is using any char/varchar columns. 2. The docs for

cfqueryparam

should be updated to indicate that the datatype you specify, may not be the datatype that is acutally used when the parameter is translated to DB. From what I can see, you could just declare all string datatypes as varchar/nvarchar and not see any different between using char/nchar/varchar/nvarchar.

Wow on that, and thanks for the clarifications. One of those "little things" where most never would fathom the impact. If you may have written up any bug reports for acf or lucee, I suspect some would be interested to follow.