Coldfusion 10中可复现的CFQUERYPARAM性能问题

Question

Coldfusion 10中可复现的CFQUERYPARAM性能问题

sql-servercoldfusionsql-server-2008-r2coldfusion-10

7

我一直能够重现Coldfusion 10查询SQL Server 2008 R2的参数化性能问题，并且很想知道其他人得到了什么结果。下面是代码。

测试在创建一个具有100行的表，其中一个数据列除外，其余都为空白。然后运行Coldfusion查询10次，一半使用cfqueryparam，一半使用简单字符串。它返回每个响应时间的列表。当我运行这个测试时，除了最初的调用之外，参数化查询的运行速度要慢得多（大约是10-100倍）。

在SQL Server中发生了什么？

我在SQL server中看不出任何区别。在两种情况下，计划缓存显示几乎相同的计划（一个显然是参数化的），而Profiler显示两者的响应速度快。但是，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#" />

- Raspin

3

我通常使用 cf_sql_numeric。这似乎可以使测试速度更快。顺便问一下，你如何从SQL Server中读取计划缓存？ - Bernhard Döbler

1

你会喜欢这个的。cf_sql_varchar也存在同样的问题（我在代码中添加了一个额外的列，但没有在这里发布）。如果我只过滤varchar，那么它的性能表现很差。然而，如果我同时过滤cf_sql_varchar和cf_sql_numeric，它就可以正常运行。这毫无意义。理论上，因为我们所有的表都有正数数字ID列，所以我可以在每个查询中添加一些愚蠢的东西，比如<>-1（不影响结果），这将有助于解决问题。但这是多么垃圾的解决方案啊！ - Raspin

1

我知道这个评论与主题无关，但是您正在使用的 SQL 语句创建了一个没有聚集索引（称为堆）的表。在99％的情况下，这不是您想要的。我不认为它会对您的问题产生影响，但要注意这一点，因为这绝对是性能差的原因之一。 - M.Scherzer

1

问题在于性能存在差异，而不是性能本身。原始数据库从中获取此测试样本，并在那里首次发现该问题，已完全索引。SQL 性能在上述两种方法之间是相同的，差异出现在 Java 或 Coldfusion 中。我认为 SQL Server 在这里无关紧要。Adobe 已经复制了这个问题（耶！）并正在调查。 - Raspin

1

我可以看到OPTION(RECOMPILE)是一个解决方案，当你有产生非常不同数据的查询（或者可能是统计数据过时了），但这个问题只是由于数据库驱动程序中的一个错误，我不想失去缓存计划的优势。我也能够在没有参数的情况下重现这个问题。当前的驱动程序偶尔会出现处理VARCHAR(MAX)的严重问题。我的主要临时解决方案是在可能的情况下截断VARCHAR。根据Adobe的说法，我们可以期待再等待几个月。 - Raspin

显示剩余13条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Robert Paulsen · Accepted Answer

答案 -- 根据评论中@Raspin的确认，设置NOCOUNT ON可以解决这些问题。

原始建议：

这可能是一个线索。你没有处理索引，但我的想法是SQL需要进行数据转换。我认为在如此少的行数情况下它不应该有影响，但我也不认为你会有这个问题：

Slow query with cfqueryparam searching on indexed column containing hashes

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

我建议尝试的另一件事是将整个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>