如何优化/重构TSQL“LIKE”子句?

使用全文搜索和 CONTAINS 在字段中间搜索时,无法优化LIKE,即当LIKE表达式以“%”开头时,因此它将始终执行完整的表扫描。

每当您使用通配符开始LIKE搜索时,您都在进行扫描。除非你能缩小搜索条件以包括第一个字符(这可能不可行),否则你需要求助于全文搜索。

你真的需要以通配符开头吗?为什么?通常,您可以强制用户至少键入第一个字符。我提出这个问题是因为一些开发人员只是习惯性地使用通配符,而不是因为有要求。在大多数情况下,用户将能够键入第一个字符,除非该字段存储了长字符串(如官方机场名称)。否则,你真的需要使用全文索引,尽管如果你不需要最后的通配符,KM的反向技巧非常酷。

如果你能避免做破坏性能的事情,那就这样做。

虽然我同意公认的答案,即全文索引将是最好的解决方案,但如果必须进行通配符搜索,我绝不提倡使用通配符 是 可以采取的潜在措施,使它们的性能不那么糟糕。

卡伦·德兰尼在书中” Microsoft SQL Server 2008 Internals

整理可以产生巨大的影响 当SQL Server必须查看几乎 字符串中的所有字符。对于 例如,请查看以下内容:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%'

使用二进制排序规则执行此操作的速度可能比使用非二进制Windows排序规则快10倍或更多。与 varchar 数据,使用SQL排序规则执行此操作的速度比使用Windows排序规则快七八倍。

我尝试了一种可能的解决方案。在此解决方案之前,即使查询也没有返回结果,并导致连接超时错误。

我的查询有日期过滤器和其他条件。所有其他条件都像搜索。一个列关键字在ntext列上搜索“%abc%”,它正在进行全表扫描。

将查询分为两部分。1)CTE(Common Table Express)中的第一部分2)在CTE上应用所有搜索条件。

WITH SearchData(Column1,Column2,Column3,Column4,........)
    AS
    (
    SELECT Column1,Column2,Column3,Column4,...........
    FROM myTable1 WITH(NOLOCK) 
            INNER JOIN MyTable2 WITH(NOLOCK) 
                ON MyTable1.id = MyTable2.Id
    WHERE (MyTable1.CreationTime >= '2014-04-27' AND MyTable1.CreationTime <= '2014-05-01') 
 )

    SELECT DISTINCT top 250 Column1,Column2,Column3,Column4
    FROM SearchData
    WHERE   (ISNULL(Column1,'') LIKE @Column1  +'%' OR @Column1 IS NULL)
            and (Column2 LIKE @Column2+ '%' OR @Column2 IS NULL)
            ...
            ...
            ...
            ...
            AND (Column10 like '%'+@Column10+'%' or @Column10 IS NULL)
            AND @Column1+@Column2+@Column3+........@Column10 <> ''  
            ORDER BY [CreationTime] DESC

它为我工作。

如果你不能使用FullTextSearch,你可以将速度提高10倍。下一步操作:

1添加计算字段:

alter table TableName
add CalculatedColumnName as upper(Column1 + '|' + Column2...) collate Latin1_General_100_Bin2
persisted;

2为计算字段添加索引:

create nonclustered index IDX_TableName_CalculatedColumnName
on TableName(CalculatedColumnName);

3更改查询文本

select count(*)
from TableName
where CalculatedColumnName like '%' + upper(@ParameterValue) + '%' collate Latin1_General_100_Bin2

来源: http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server

怎么样

field1 + field2 + field3 LIKE '%' + @DESC + '%'

或

CONTAINS(field1 + field2 + field3, @DESC)