Coldfusion 10中可重现的CFQUERYPARAM性能问题 [英] Reproducible CFQUERYPARAM performance issue in Coldfusion 10

问题描述

我一直能够通过Coldfusion 10来查询SQL Server 2008 R2来重现严重的参数化性能问题，并且想知道其他人会得到什么。代码如下。

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.

测试是做什么的？
它创建了一个包含100行的表。除一个以外，所有数据列均为空白。然后，它将运行一次Coldfusion查询10次，一半使用cfqueryparam，另一半使用一个简单的字符串。它返回一个列表，其中包含每个列表的响应时间。当我运行此命令时，除初始调用外，参数化查询的运行速度要慢得多（大约10到100次）。

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

SQL Server中发生了什么？
我看不到SQL Server的区别。在这两种情况下，计划缓存均指示几乎相同的计划（显然已对一个参数进行了参数化），并且探查器显示了针对这两个计划的快速响应。但是，Coldfusion很难处理参数化查询。

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.

是什么解决了这个问题？
奇怪的是，如果我将varchar更改为nvarchar， ，问题就解决了。或者，如果我将非空白移动到开始，那么两个响应都将很慢（如图）。如果我将所有记录都留为空白或非空白，那么问题就不存在了。它一定是混合的。我无法在CF9中重现该问题，但没有尝试过CF11。

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

推荐答案

答案-由@Raspin确认在评论中，设置 NOCOUNT ON 即可解决问题。

ANSWER -- As confirmed by @Raspin in the comments, setting NOCOUNT ON fixes the issues.

原始建议：

这可能是一个线索。您无需处理INDEX，但我认为SQL必须进行数据转换。我认为行数这么少不会有问题，但我也不认为您会遇到这个问题：

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:

使用cfqueryparam在包含哈希的索引列上进行慢速查询

可能发生的事情是，如果cfqueryparam是否将varchars作为unicode发送，则ColdFusion管理员中有一个设置。如果该设置与列设置不匹配（在您的情况下，如果启用了该设置），则MS SQL将不使用该索引。

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.

我建议尝试的另一件事是将整个 SELECT 语句包装在 IF 语句。我的想法是，它可能以SQL认为无法重用查询计划的方式出现。这意味着您的性能损失实际上是重新编译：

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>

这篇关于Coldfusion 10中可重现的CFQUERYPARAM性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！