使用 cfqueryparam 在包含哈希的索引列上搜索慢速查询 [英] Slow query with cfqueryparam searching on indexed column containing hashes

问题描述

我有以下查询，运行时间为 16 毫秒 - 30 毫秒.

I have the following query that runs in 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 毫秒内运行.

If I execute the same query but use cfqueryparam it runs in 500ms - 2000ms.

<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.Web 服务器运行的是最新版本的 CF9.

The table has roughly 60,000 rows. The "hash" column is varchar(50) and has a unique non-clustered index, but is not the primary key. DB server is MSSQL 2008. The web server is running the latest version of CF9.

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

Any idea why the cfqueryparam causes the performance to bomb out? It behaves this way every single time, no matter how many times I refresh the page. If I pair the list down to only 2 or 3 hashes, it still performs poorly at like 150-200ms. When I eliminate the cfqueryparam the performance is as expected. In this situation there is the possibility for SQL injection and thus using cfqueryparam would certainly be preferable, but it shouldn't take 100ms to find 2 records from an indexed column.

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

1. We are using hashes generated by hash() not UUIDS or GUIDS. The hash is generated by a hash(SerializeJSON({ struct })) which contains the plan for a set of operations to execute on an image. The purpose for this is that it allows us to know before insert and before query the exact unique id for that structure. These hashes act as an "index" of what structures have already been stored in the DB. In addition with hashes the same structure will hash to the same result, which is not true for UUIDS and GUIDS.

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

The query is being executed on 5 different CF9 servers and all of them exhibit the same behavior. To me this rules out the idea that CF9 is caching something. All servers are connecting to the exact same DB so if caching was occurring it would have to be the DB level.

推荐答案

您的问题可能与 VARCHAR 与 NVARCHAR 有关.这 2 个链接可能会有所帮助从 ColdFusion 查询 MS SQL Server G/UUID 和nvarchar 与 SQL Server 中的 varchar，注意

Your issue may be related to VARCHAR vs NVARCHAR. These 2 links may help Querying MS SQL Server G/UUIDs from ColdFusion and nvarchar vs. varchar in SQL Server, BEWARE

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

What might be happening is there is a setting in ColdFusion administrator if cfqueryparam sends varchars as unicode or not. If that setting does not match the column setting (in your case, if that setting is enabled) then MS SQL will not use that index.

这篇关于使用 cfqueryparam 在包含哈希的索引列上搜索慢速查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！