sql-server - 使用 cfqueryparam 搜索包含哈希的索引列的慢速查询

我有以下查询在 16ms - 30ms 内运行。

<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 行。 “哈希”列是 varchar(50) 并且具有唯一的非聚集索引，但不是主键。数据库服务器是 MSSQL 2008。网络服务器运行最新版本的 CF9。

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

编辑:

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

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

最佳答案

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

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

关于sql-server - 使用 cfqueryparam 搜索包含哈希的索引列的慢速查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10543755/