SQL Server:如何优化“喜欢"查询? [英] SQL Server: how to optimize "like" queries?
问题描述
我有一个查询,它使用带有通配符的like"来搜索客户端.例如:
I have a query that searches for clients using "like" with wildcard. For example:
SELECT TOP (10)
[t0].[CLIENTNUMBER],
[t0].[FIRSTNAME],
[t0].[LASTNAME],
[t0].[MI],
[t0].[MDOCNUMBER]
FROM [dbo].[CLIENT] AS [t0]
WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0')
AND ([t0].[FIRSTNAME] LIKE '%John%')
AND ([t0].[LASTNAME] LIKE '%Smith%')
AND ([t0].[SSN] LIKE '%123%')
AND ([t0].[CLIENTNUMBER] LIKE '%123%')
AND ([t0].[MDOCNUMBER] LIKE '%123%')
AND ([t0].[CLIENTINDICATOR] = 'ON')
它也可以在where"子句中使用较少的参数,例如:
It can also use less parameters in "where" clause, for example:
SELECT TOP (10)
[t0].[CLIENTNUMBER],
[t0].[FIRSTNAME],
[t0].[LASTNAME],
[t0].[MI],
[t0].[MDOCNUMBER]
FROM [dbo].[CLIENT] AS [t0]
WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0')
AND ([t0].[FIRSTNAME] LIKE '%John%')
AND ([t0].[CLIENTINDICATOR] = 'ON')
谁能说出优化此类查询性能的最佳方法是什么?也许我需要创建一个索引?此表在生产中最多可以有 1000K 条记录.
Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.
推荐答案
要为模式具有 '%XXX%'
形式的 LIKE
做很多事情,你想查询SQL Server的全文索引能力,用CONTAINS
代替LIKE
.按原样,您正在执行全表扫描,因为普通索引不会帮助搜索以通配符开头的项目 - 但全文索引会.
To do much for a LIKE
where the pattern has the form '%XXX%'
, you want to look up SQL Server's full-text indexing capability, and use CONTAINS
instead of LIKE
. As-is, you're doing a full table scan, because a normal index won't help with a search for an item that starts with a wild card -- but a full-text index will.
/* ... */
WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0')
AND (contains([t0].[FIRSTNAME], 'John'))
AND (contains([t0].[LASTNAME], 'Smith'))
AND (contains([t0].[SSN], '123'))
AND (contains([t0].[CLIENTNUMBER],'123'))
AND (contains([t0].[MDOCNUMBER], '123'))
AND ([t0].[CLIENTINDICATOR] = 'ON')
这篇关于SQL Server:如何优化“喜欢"查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!