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#" />

Have a look at the execution plan for each version of the test, and see if that casts any light on the scene? — Adam Cameron, Aug 12 '15 at 11:39
I did. I emptied the plan cache and ran these two queries. There were two in there. They were identical apart from one being parameterized. I've also mentioned that the profiler shows SQL performing identically. It's either ColdFusion or the database interface. However, I should add that when the test is run from .NET all is fine (this likely uses the same ODBC - although obviously not Java). — Raspin, Aug 12 '15 at 11:58
I usually use `cf_sql_numeric`. That seems to make your test faster. Btw. how do you read the plan caches from SQL Server? — Bernhard Döbler, Aug 12 '15 at 12:20
Thanks I do like the suggestion of using cf_sql_numeric. It does fix the issue. A few other changes also fix the issue (I've mentioned some above and also changing the varchar(max) to a varchar(n)!) However, this might be the simplest amendment as we use a single database scaffold for 30 websites. But I would have to check the performance as the risk to 30 big clients is enormous (see Adam's comment in https://forums.adobe.com/thread/1270814?start=0&tstart=0). — Raspin, Aug 12 '15 at 12:54
Even if the cf_sql_numeric is a viable fix for me I will still likely pass this to Adobe as something odd is going on. The sample code I've given represents some very common queries in our system (which were running stupidly slow). — Raspin, Aug 12 '15 at 12:54
I use this to query the plan cache. Not sure if it uses any third party procedures: select text,query_plan,usecounts from sys.dm_exec_cached_plans decp cross apply sys.dm_exec_sql_text(decp.plan_handle) dest cross apply sys.dm_exec_query_plan(decp.plan_handle) deqp — Raspin, Aug 12 '15 at 12:55
*I change the varchar to an nvarchar* Just to clarify, do you mean [`nvarchar`](http://stackoverflow.com/questions/10802388/what-are-the-details-for-using-cf-sql-nvarchar-in-coldfusion-10/) or [`varchar(n)`](https://msdn.microsoft.com/en-us/library/ms187752.aspx) ie max length 8000? — Leigh, Aug 12 '15 at 13:34
I meant varchar with any non max number. e.g. varchar(100). nvarchar also fixes it (max or not). — Raspin, Aug 12 '15 at 13:46
You're gonna love this. The same issue exists for cf_sql_varchar (I added an extra column to the code, but not posted here). If I filter on just the varchar then it performs poorly. However, if I filter on cf_sql_varchar AND cf_sql_numeric, it runs fine. This makes no sense. In theory because all our tables have positive numeric ID columns I could add something silly like <>-1 to every query (without affecting the result) and it would help fix the issue. But what a rubbish solution is that?! — Raspin, Aug 12 '15 at 13:47
I added sleep(250) between each select query and additionally tested this using CF9. All times were somewhat consistent in CF9, but CF10 INT is consistently slower. — James Moberg, Aug 12 '15 at 16:22
Unsurprisingly a stored procedure works fine (with either data type), even though it effectively parameterizes. It just adds weight to the weirdness of ColdFusion's handling of the dataset. I'm sending off to Adobe and will post back here any response. — Raspin, Aug 13 '15 at 09:52
I know this comment is offtopic but with the SQL you are using you are creating a table without a clustered index (called a heap). In 99% of cases that is not what you want. I don't think it will make a difference to your issue buts it's something to be aware off as that definitely is a cause of poor performance. — M.Scherzer, Aug 14 '15 at 12:34
The issue here is a difference in performance, not necessarily the performance itself. The original database, from which this test sample is derived and where the issue was first identified, is fully indexed. SQL performance is identical between the two methods above, the difference arises in Java or Coldfusion. SQL Server is off the hook here, I think. Adobe have reproduced the issue (yay!) and are investigating. — Raspin, Aug 15 '15 at 13:28
I am eager for their official response. Although not a direct hit to the solution, but [a mixin of SQL query string and cfqueryparam](http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=no_cfqueryparam) and [Data Binding Without Using Cfqueryparam](http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=no_cfqueryparam) approaches should help form a solution. — Anurag, Sep 04 '15 at 06:10
I'm still waiting. Heard nothing back despite chasing them. We're living with the performance hit for now since a rewrite would be very far from straightforward and seriously hit testing. — Raspin, Sep 11 '15 at 10:55
Latest update from Adobe is the fix is dependent on an external component, which would imply they are waiting on a third party fix. — Raspin, Oct 21 '15 at 08:49
I had a similar issue a while back and found that the problem went away if I added `OPTION (RECOMPILE)` to the end of the `SELECT` statement. There was a post on an SQL server forum that explained what was going on there but I can't seem to find it now. — Gary Stanton, Nov 17 '15 at 13:06
I could see OPTION (RECOMPILE) being a solution where you've got queries that produce wildly different data (or the stats are out of date maybe) but this issue is simply down to a bug in the database driver and I wouldn't want to lose the advantage of cached plans. I've also been able to reproduce the problem without parameters. The current driver has occasionally serious issues handling VARCHAR(MAX). My main temporary solution is to truncate the VARCHARs where possible. According to Adobe we can expect to wait another couple of months. — Raspin, Nov 19 '15 at 14:34

score 2 · Accepted Answer · edited May 23 '17 at 11:45

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>

Well I've just tried both. Changing the High ASCII/Unicod setting and moving the IF statement. Neither makes a difference on the test above. But the latest here is I'm still waiting on Adobe. They've gone radio silent, closed my ticket and not responded to any emails. I'm currently persona non grata it would seem. — Raspin, Mar 21 '16 at 14:10
Since it's a network driver-thing, some thoughts: (1) you could try a connection string that would use TCP/IP instead of named pipes, (2) add SET NOCOUNT ON -- this will change what goes across the wire, maybe working around whatever issue you're hitting. (3) I wonder what is happening to the dataset that is being returned but not read. Maybe they are 'queuing up' in the driver and you should try reading the datasets as they are returned. — Robert Paulsen, Mar 22 '16 at 19:00
Well, you might want to add your comment as an answer so I can mark it so. SET NOCOUNT ON indeed does fix it. I must admit I did at first have my doubts but only took a few seconds to test. Indeed it does seem to avoid the issue. Who knows what's going on with the driver but clearly it's happier without the row count! I'll add to our code base and hopefully it won't create any unforeseens. Thanks. — Raspin, Mar 24 '16 at 09:01

Reproducible CFQUERYPARAM performance issue in Coldfusion 10

1 Answers1