类似 % 的 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屋!