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

问题描述

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

 < cfquery name =local.test1datasource =imagecdn> 
选择哈希乔布斯WHERE散列（
'EBDA95630915EB80709C69089315399B'，
'3617B8E6CF0C62ECBD3C48DDF8585466'，
'D519A38F09FDA868A2FEF1C55C9FEE76'，
'135F94C3774F7719CFF8FF3A275D2D05'，
'D58FAE69C559273D8427673A08193789 '，
'2BD7276F209768F2FCA6635659D7922A'，
'B1E3CFBFCCFF6F5B48A849A050E6D424'，
'2288F5B8A797F5302E8CA24323617236'，
'8951883E36B5D38A4643DFAA0396BF13'，
'839210BD564E30BE1355D1A6D4EF7081'，
'ED4A2CB0C28B608C29576819CF7BE19B'， 
'CB26925A4874945B810707D5FF0B91F2'，
'33B2FC229F0CC797A02AD163CDBA0875'，
'624986E7547DBAC0F47B3005CFDE0A16'，
'6F692C289BD805CEE41EF59F83F16F4D'，
'8551F0033C617BD9EADAAD6CEC4B3E9E'，
'94C3C0A74C2DE085FF9F1BBF928821A4'，
'28DC1A9D2A69C2EDF5E6C0E6368A0B3C'
）
< / cfquery> 
 

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

 < cfset local.hashes =[与上述相同的ID列表]> 
< cfquery name =local.test2datasource =imagecdn> 
 SELECT hash FROM jobs WHERE hash in（
< cfqueryparam cfsqltype =cf_sql_varcharvalue =＃local.hashes＃list =yes> 
）
< ; / cfquery> 
  

表大约有60,000行。 hash列是varchar（50），并且具有唯一的非聚集索引，但不是主键。 DB服务器是MSSQL 2008. Web服务器运行的是最新版本的CF9。

任何想法为什么cfqueryparam会导致性能爆炸？它每个时间表现这种方式，无论我刷新页面多少次。如果我把列表配对只有2或3哈希，它仍然执行不好，像150-200ms。当我消除cfqueryparam的性能是预期的。在这种情况下，SQL注入的可能性，因此使用cfqueryparam肯定会更好，但它不应该花费100毫秒从一个索引列中找到2条记录。

修改：

1. 我们使用 hash / code>不是UUIDS或GUIDS。散列由散列（SerializeJSON（{struct}））生成，该散列包含要在图像上执行的一组操作的计划。这样做的目的是，它允许我们在插入前和查询之前知道该结构的确切唯一ID。这些散列充当已经存储在DB中的什么结构的索引。




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




解决方案

您的问题可能与VARCHAR与NVARCHAR相关。这两个链接可能有助于
从ColdFusion查询MS SQL Server G / UUID 和
在SQL Server中的nvarchar vs. varchar，BEWARE <如果 cfqueryparam 将varchars作为unicode发送或不发送，ColdFusion管理员中会有一个设置。如果该设置与列设置不匹配（在您的情况下，如果启用了该设置），则MS SQL将不使用该索引。

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>

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>

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.

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.

Edits:

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.

2. 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.

解决方案

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

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屋！