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

Question

>我有以下查询，在 16 毫秒 - 30 毫秒内运行。

<cfquery name="local.test1" datasource="imagecdn">
    SELECT hash FROM jobs WHERE hash in(
        'EBDA95630915EB80709C69089315399B',
        '3617B8E6CF0C62ECBD3C48DDF8585466',
        'D519A38F09FDA868A2FEF1C55C9FEE76',
        '135F94C3774F7719CFF8FF3A275D2D05',
        'D58FAE69C559273D8427673A08193789',
        '2BD7276F209768F2FCA6635659D7922A',
        'B1E3CFBFCCFF6F5B48A849A050E6D424',
        '2288F5B8A797F5302E8CA24323617236',
        '8951883E36B5D38A4643DFAA0396BF13',
        '839210BD564E30BE1355D1A6D4EF7081',
        'ED4A2CB0C28B608C29576819CF7BE19B',
        'CB26925A4874945B810707D5FF0B91F2',
        '33B2FC229F0CC797A02AD163CDBA0875',
        '624986E7547DBAC0F47B3005CFDE0A16',
        '6F692C289BD805CEE41EF59F83F16F4D',
        '8551F0033C617BD9EADAAD6CEC4B3E9E',
        '94C3C0A74C2DE085FF9F1BBF928821A4',
        '28DC1A9D2A69C2EDF5E6C0E6368A0B3C'
    )
</cfquery>

如果我执行相同的查询但使用 cfqueryparam，它会在 500 毫秒 - 2000 毫秒内运行。

<cfset local.hashes = "[list of the same ids as above]">
<cfquery name="local.test2" datasource="imagecdn">
    SELECT hash FROM jobs WHERE hash in(
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#local.hashes#" list="yes">
    )
</cfquery>

该表大约有 60,000 行。"hash" 列是 varchar(50(，具有唯一的非聚集索引，但不是主键。数据库服务器是MSSQL 2008。Web 服务器正在运行最新版本的 CF9。

知道为什么 cfqueryparam 会导致性能爆炸吗？它每次都以这种方式运行，无论我刷新页面多少次。如果我将列表仅配对为 2 或 3 个哈希，它在 150-200 毫秒时仍然表现不佳。当我消除 cfqueryparam 时，性能符合预期。在这种情况下，有可能进行SQL注入，因此使用cfqueryparam肯定是可取的，但是从索引列中找到2条记录不应该花费100ms。

编辑：

我们使用的哈希值由

1. hash()而不是 UUID 或 GUIDS 生成。哈希由一个hash(SerializeJSON({ struct }))生成，该包含要对映像执行的一组操作的计划。这样做的目的是，它允许我们在插入之前和查询之前知道该结构的确切唯一 id。这些哈希充当已存储在数据库中的结构的"索引"。此外，对于哈希，相同的结构将哈希为相同的结果，这对于 UUID 和 GUIDS 则不然。

2. 查询在 5 个不同的 CF9 服务器上执行，并且所有服务器都表现出相同的行为。对我来说，这排除了 CF9 正在缓存某些东西的想法。所有服务器都连接到完全相同的数据库，因此如果发生缓存，则必须是数据库级别。

Answer 1

您的问题可能与 VARCHAR 与 NVARCHAR 有关。这两个链接可能会有所帮助从 ColdFusion 查询 MS SQL Server G/UUID 和nvarchar vs. varchar in SQL Server， Beware

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

Answer 2

正如 Mark 指出的那样，缓存中的执行计划可能很糟糕。cfqueryparam 的一个优点是，当您传入不同的值时，它可以重用该语句的缓存计划。这就是为什么当您使用较小的列表尝试时，您看不到任何改进。当你不使用cfqueryparam时，SQL Server每次都必须制定出执行计划。这通常是一件坏事，除非它在缓存中有一个次优计划。尝试按照此处的说明清除缓存 http://www.devx.com/tips/Tip/14401 希望这意味着下次运行带有 cfqueryparam 的语句时，它将缓存更好的计划。

有意义？

Answer 3

我不认为cfqueryparam会导致问题。正如您提到的执行力大幅增加，在尝试使用 cfqueryparam 时，索引可能不会用于您的查询。我在开发计算机上创建了相同的场景，但是无论使用还是不使用cfqueryparam，我的执行时间都相同。使用列表可能会有一些开销，因为在第一个查询中，您直接将其作为测试传递，而在第二个冷融合中需要从提供的列表中的查询参数创建，但这应该不会那么多。我建议启动"SQL Server Profiler"并监视在服务器上执行的查询，这将使您更好地花费另外 500 毫秒。