可再生CFQUERYPARAM性能问题

Question

我一直能够重现Coldfusion10查询Server 2008 R2时出现的一个严重的参数化性能问题，并且有兴趣知道其他人得到了什么。代码在下面。

测试是做什么的？，它创建了一个100行的表。除了一个列之外，数据列都是空的。然后，它运行一个Cold聚变查询10次，其中一半使用cfqueryparam，另一半使用简单字符串。它返回一个包含每个响应时间的列表。当我运行这个函数时，除了最初的调用之外，参数化查询的运行速度要慢得多(大约运行10-100次)。

在server中发生了什么？我可以看到在server中没有什么不同。在这两种情况下，计划缓存都表示几乎相同的计划(其中一个显然是参数化的)，分析器显示两者的快速响应。但是，Coldfusion与参数化查询进行了斗争。

什么解决了这个问题？好奇地说，如果我把varchar改为nvarchar，问题就消失了。或者，如果我将非空白移动到开始，那么两种反应都是缓慢的(围棋图)。如果我把所有的记录都变成空白或非空白，那么这个问题就不存在了。一定是混在一起的。我无法在CF9中重现这个问题，但还没有尝试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

答案--正如@Raspin在评论中所确认的，设置修复了问题。

原建议：

这可能是条线索。您不是在处理索引，但我的想法是SQL必须进行数据转换。我不认为这么少的行会有什么关系，但我也不会认为你会有这个问题：

Slow query with cfqueryparam searching on indexed column containing hashes

可能发生的情况是，如果cfqueryparam将varchars发送为unicode，则ColdFusion管理员中有一个设置。如果该设置与列设置不匹配(在您的示例中，该设置已启用)，则MS将不使用该索引。

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

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