Reproducible CFQUERYPARAM performance issue in Coldfusion 10

Question

I was able to consistently reproduce the serious parameterization performance issue with Coldfusion 10 with a SQL Server 2008 R2 query, and it would be interesting to see what others get. The code is below.

What does the test do? It creates a table with 100 rows. The data column is empty in all but one. He then runs the Coldfusion query 10 times, half using cfqueryparam and half using a simple string. It returns a list with response time for each. When I run this, in addition to the initial calls, the parameterized query is much slower (about 10-100 times).

What is happening in SQL Server? I do not see the difference in the SQL server. In both cases, the plan cache indicates almost identical plans (one is obviously parameterized), and the profiler shows quick answers for both. However, Coldfusion is struggling with a parameterized query.

What fixes the problem? Curiously, if I change varchar to nvarchar, the problem will disappear. Or, if I move the nonempty to the beginning, then both answers are slow (go figure). If I make all entries empty or not empty, then again the problem will not be. It must be a mixture. I can not reproduce the problem in CF9, but have not tried CF11.

<cfset datasource="yourdatasource" /> <cfquery name="createdata" datasource="#datasource#"> --EMPTY PREVIOUS TESTS IF OBJECT_ID('aaatest', 'U') IS NOT NULL BEGIN TRUNCATE TABLE aaatest; DROP TABLE aaatest; END --CREATE TABLE TO CONTAIN DATA CREATE TABLE [dbo].[aaatest]( [id] [int] NOT NULL, [somedata] [varchar](max) NULL, [somekey] [int] NOT NULL ) ON [PRIMARY]; --INSERT 100 ROWS WITH 99 BLANK AND 1 NON-BLANK WITH datatable AS ( SELECT 1 id UNION all SELECT id + 1 FROM datatable WHERE id + 1 <= 100 ) INSERT INTO aaatest(id,somekey,somedata) SELECT id,1,case when id=99 then 'A' else '' end FROM datatable; </cfquery> <cfset results=[] /> <cfloop from="1" to="10" index="n"> <!--- use parameters for every other test ---> <cfset useParameters = (n mod 2 is 0) /> <cfquery name="myquery" datasource="#datasource#" result="result"> SELECT somedata FROM aaatest WHERE somekey= <cfif useParameters> <cfqueryparam value="1" CFSQLType="CF_SQL_INTEGER" /> <cfelse> 1 </cfif> </cfquery> <!--- store results with parameter test marked with a P ---> <cfset arrayAppend(results,(useParameters?'P':'')&result.executiontime) /> </cfloop> <cfdump var="#results#" /> 

Answer 1

ANSWER. As @Raspin confirmed in the comments, setting NOCOUNT ON fixes the problems.

Original offers:

This may be the key. You are not dealing with INDEX, but I think SQL should do the data conversion. I would not have thought that it would matter with so many lines, but I also don’t think you would have such a problem:

Slow query with cfqueryparam lookup in indexed column containing hashes

What can happen is there is a parameter in the ColdFusion admin if cfqueryparam sends varchars as unicode or not. If this parameter does not match the column setting (in your case, if this parameter is enabled), MS SQL will not use this index.

Another thing I suggest trying is to wrap the entire SELECT in an IF . My thought is that perhaps this is happening so that SQL does not consider it possible to reuse the query plan. This means that performance loss is actually a recompilation:

 <cfloop from="1" to="10" index="n"> <cfset useParameters = (n mod 2 is 0) /> <cfif useParameters> <cfquery name="myquery" datasource="#datasource#" result="result"> SELECT somedata FROM aaatest WHERE somekey= <cfqueryparam value="1" CFSQLType="CF_SQL_INTEGER" /> </cfquery> <cfelse> <cfquery name="myquery" datasource="#datasource#" result="result"> SELECT somedata FROM aaatest WHERE somekey= 1 </cfquery> </cfif> <cfset arrayAppend(results,(useParameters?'P':'')&result.executiontime) /> </cfloop>