类似 % 的 SQL Server 性能提示 [英] SQL Server Performance tips for like %

问题描述

我有一个包含 5-1000 万条记录的表，其中包含 2 个字段

I have a table with 5-10 million records which has 2 fields

示例数据

Row  Field1   Field2
------------------
1    0712334  072342344
2    06344534 083453454
3    06344534 0845645565

给定 2 个变量

variable1 : 0634453445645
variable2 : 08345345456756

我需要能够尽快查询表格以获得最佳匹配

I need to be able to query the table for best matches as fast as possible

上面的例子将产生 1 条记录(例如第 2 行)

The above example would produce 1 record (e.g row 2)

查询数据库匹配项的最快方法是什么?

What would be the fastest way to query the database for matches?

注意:数据和变量总是采用这种格式(即总是一个数字，可能有也可能没有前导零，字段不是唯一的，但是两者的组合会是 )

Note : the data and variables are always in this format (i.e always a number, may or may not have a leading zero, and fields are not unique however the combination of both will be )

我最初的想法是做这样的事情

My initial thought was to do something like this

Select blah where Field1 + "%" like variable1 and  Field2 + "%" like variable2

如果我的伪代码不正确，请原谅我的伪代码，因为这更像是一个事实调查任务.不过我想我在球场上.

Please forgive my pseudo-code if it's not correct, as this is more a fact-finding mission. However I think I'm in the ball park.

注意:我认为这里没有任何索引可以提供帮助，尽管我猜基于内存的表会加快速度.

Note : I don't think any indexing can help here, though a memory-based table I'm guessing would speed this up.

谁能想到更好的方法来解决这个问题?

Can anyone think of a better way of solving the problem?

推荐答案

您可以通过这样的查询在 Field1 上的索引上寻找一个计划.

You can get a plan with a seek on an index on Field1 with query like this.

declare @V1 varchar(20) = '0634453445645'
declare @V2 varchar(20) = '08345345456756'

select Field1,
       Field2
from YourTable
where Field1 like left(@V1, 4) + '%' and
      @V1 like Field1 + '%' and
      @V2 like Field2 + '%'

它对 Field1 的前四个字符进行范围查找，并在残差谓词中使用 Field1 和 Field2 的完整比较.

It does a range seek on the first four characters on Field1 and uses the full comparison on Field1 and Field2 in a residual predicate.

这篇关于类似 % 的 SQL Server 性能提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！