CHARINDEX 与 LIKE 搜索给出了非常不同的性能，为什么? [英] CHARINDEX vs LIKE search gives very different performance, why?

问题描述

我们使用实体框架进行数据库访问，当我们思考"LIKE 语句时 - 它实际上生成了 CHARINDEX 内容.所以，这里有 2 个简单的查询，在我简化它们以证明我们特定服务器上的一点之后:

We use Entity Frameworks for DB access and when we "think" LIKE statement - it actually generates CHARINDEX stuff. So, here is 2 simple queries, after I simplified them to prove a point on our certain server:

-- Runs about 2 seconds
SELECT * FROM LOCAddress WHERE Address1 LIKE '%1124%' 
-- Runs about 16 seconds
SELECT * FROM LOCAddress WHERE ( CAST(CHARINDEX(LOWER(N'1124'), LOWER([Address1])) AS int)) = 1

表现在包含大约 10 万条记录.Address1 是 VarChar(100) 字段，没什么特别的.

Table contains about 100k records right now. Address1 is VarChar(100) field, nothing special.

这是并排的 2 个计划的片段.没有任何意义，显示 50% 和 50% 但执行时间为 1:8

Here is snip of 2 plans side by side. Doesn't make any sense, shows 50% and 50% but execution times like 1:8

我在网上搜索，一般建议是使用 CHARINDEX 而不是 LIKE.根据我们的经验，情况正好相反.我的问题是导致这种情况的原因是什么，我们如何在不更改代码的情况下修复它?

I searched online and general advice is to use CHARINDEX instead of LIKE. In our experience it's opposite. My question is what causing this and how we can fix it without code change?

推荐答案

我将回答我自己的问题，因为很难找到正确的答案，并且 SQL Server 2012 执行计划输出指出了这个问题.正如您在原始问题中看到的那样 - 表面上看起来一切正常.这是 SQL Server 2008.

I will answer my own question since it was hard to find correct answer and I was pointed to the problem by SQL Server 2012 Execution Plan output. As you see in original question - everything looks OK on surface. This is SQL Server 2008.

当我在 2012 年运行相同的查询时，我收到了关于 CHARINDEX 查询的警告.问题是 - SQL Server 必须进行类型转换.Address1 是 VarChar 并且查询有 N'1124'，它是 Unicode 或 NVarChar.如果我这样更改此查询:

When I run same query on 2012 I got warning on CHARINDEX query. Problem is - SQL Server had to do type conversion. Address1 is VarChar and query has N'1124' which is Unicode or NVarChar. If I change this query as so:

SELECT * 
FROM LOCAddress 
WHERE (CAST(CHARINDEX(LOWER('1124'), LOWER([Address1])) AS int)) 

然后运行与 LIKE 查询相同的查询.因此，由实体框架生成器引起的类型转换导致了这种可怕的性能下降.

It then runs same as LIKE query. So, type conversion that was caused by Entity Framework generator was causing this horrible hit in performance.

这篇关于CHARINDEX 与 LIKE 搜索给出了非常不同的性能，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！