sql-server - 使用 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) 并具有唯一的非聚集索引，但不是主键。 DB服务器是MSSQL 2008。Web服务器运行最新版本的CF9。

知道为什么 cfqueryparam 会导致性能崩溃吗？无论我刷新页面多少次，它每次都会以这种方式运行。如果我将列表配对为仅 2 或 3 个哈希值，它的性能仍然很差，大约 150-200 毫秒。当我消除 cfqueryparam 时，性能符合预期。在这种情况下，存在 SQL 注入(inject)的可能性，因此使用 cfqueryparam 肯定会更好，但从索引列中查找 2 条记录不应花费 100 毫秒。

编辑:

1. 我们使用由 hash() 生成的哈希值，而不是 UUIDS 或 GUIDS。哈希由 hash(SerializeJSON({ struct })) 生成，其中包含在图像上执行的一组操作的计划。这样做的目的是让我们在插入之前和查询之前知道该结构的确切唯一 ID。这些哈希值充当数据库中已存储结构的“索引”。此外，对于哈希，相同的结构将哈希得到相同的结果，但对于 UUIDS 和 GUIDS 来说并非如此。

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

Answer 1

您的问题可能与 VARCHAR 与 NVARCHAR 有关。这 2 个链接可能有帮助 Querying MS SQL Server G/UUIDs from ColdFusion和 nvarchar vs. varchar in SQL Server, BEWARE

可能发生的情况是，ColdFusion 管理员中有一个设置，说明 cfqueryparam 是否以 unicode 形式发送 varchar。如果该设置与列设置不匹配(在您的情况下，如果启用该设置)，则 MS SQL 将不会使用该索引。