cfqueryparam converting varchar to nvarchar in sql azure

Question

I'm using a database in Azure with ColdFusion 2016. I'm using Sqljdbc41.jar from (helpful links on how to configure it: link1 link2) This is my query. [uuid] has an index on it and is varchar(36) and pkIdentity is a primary key int.

select pkIdentity
from tbl1
where [uuid] = <cfqueryparam cfsqltype="cf_sql_varchar" value="#attributes.uuid#" maxlength="36">

This query is consuming the most DTUs in Azure. Coldfusion is sending it to Azure as

(@P0 nvarchar(4000))select pkIdentity
from tbl1
where [uuid]= @P0

I've read that a setting on the jdbc driver may be casting the varchar datatype as varchar when passing it to Azure SQL. However, I don't have the option in the CF database setup screen to disable the conversion to nvarchar.

I think these are my options. Which do you think is better?

try to reverse engineer what coldfusion is doing when you use cfqueryparam but specify the correct datatype (use sp_prepexec ?)
remove use of cfqueryparam totally and just validate that string is valid uuid before hard coding it into query (ex. where [uuid] = '#attributes.uuid#') but then I'm afraid I'd lose visiblity to all the executions of this query being grouped together within the Azure SQL Performance Insight tool

How did you ascertain your statement about how ColdFusion is sending the parameter? — Dan Bracuk, Aug 31 '17 at 15:14
from this answer - https://stackoverflow.com/questions/10802388/what-are-the-details-for-using-cf-sql-nvarchar-in-coldfusion-10/10848136#10848136 — jessieloo, Aug 31 '17 at 15:21
Do you believe the conversion to nvarchar slows the query down? http://www.jochenhebbrecht.be/site/2014-05-01/java/fixing-slow-queries-running-sql-server-using-jpa-hibernate-and-jtds `jdbc:sqlserver://localhost\SQLEXPRESS;DatabaseName=TESTDB;sendStringParametersAsUnicode=false` — Bernhard Döbler, Aug 31 '17 at 15:32
You don't need to rely solely on the Azure SQL Performance Insight Tool you mentioned. You have the option to use Query Store https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store — Alberto Morillo, Aug 31 '17 at 15:59
If the problem is a setting somewhere, I suggest trying harder to get that setting changed. — Dan Bracuk, Aug 31 '17 at 16:15
Leaning towards @DanBracuk's response, I would also move off of the SQL JDBC jar that you're using in favor of the native SQL Server drivers that ship with CF2016. My previous employer moved from CF 8 to 9 to 2016 over the last 8 years. We made the move off of that JAR file when converting to CF 9. Had to make some code changes to account for how a few things were handled at the time, but it's a change we needed to make for performance and stability reasons. — Adrian J. Moreno, Sep 02 '17 at 20:45

score 1 · Accepted Answer · answered Sep 05 '17 at 15:36

Many thanks to @BernhardDöbler for commenting with the parameter I needed to correct the issue of the datasource always casting parameters in Unicode. The parameter that needs added to the JDBC URL is sendStringParametersAsUnicode=false (Ironically I first copied it from his comment and added it to the setup but it didn't work b/c there were some strange characters embedded between a couple of letters.) The change in DTU consumption is significantly lower now! See screenshots below.

cfqueryparam converting varchar to nvarchar in sql azure

1 Answers1