Reproducible CFQUERYPARAM performance issue in Coldfusion 10

Question

I've consistently been able to reproduce a serious parameterization performance issue with Coldfusion 10 querying SQL Server 2008 R2 and would be interested to know what others get. The code is below.

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

What's happening in SQL Server? I can see no difference in SQL server. In both cases the plan cache indicates virtually identical plans (one is obviously parameterized) and the profiler shows fast responses for both. However, Coldfusion struggles with the parameterized query.

What fixes the issue? Curiously, if I change the varchar to an nvarchar, the problem goes away. Or if I move the non-blank to the start, then both responses are slow (go figure). If I make all records blank or non-blank then again the issue isn't there. It must be a mix. I can't reproduce the issue in CF9 but haven't 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 confirmed by @Raspin in the comments, setting NOCOUNT ON fixes the issues.

Original Suggestions:

This might be a clue. You're not dealing with an INDEX, but my thought is that SQL is having to do a data conversion. I wouldn't think it would matter with so few rows, but I also wouldn't think you would have this problem:

Slow query with cfqueryparam searching on indexed column containing hashes

What might be happening is there is a setting in ColdFusion administrator if cfqueryparam sends varchars as unicode or not. If that setting does not match the column setting (in your case, if that setting is enabled) then MS SQL will not use that index.

The other thing I would suggest trying is wrapping your entire SELECT statement in the IF statement. My thought is that maybe its coming across in a way that SQL doesn't think it can re-use the query plan. That means your loss of performance is actually a recompile:

<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>