Как я могу оптимизировать/рефакторить предложение TSQL LIKE?

Используйте полнотекстовый поиск и СОДЕРЖИТ. LIKE нельзя оптимизировать при поиске в середине поля, т.е. когда выражение LIKE начинается с «%», оно всегда будет выполнять полное сканирование таблицы.

Каждый раз, когда вы начинаете поиск LIKE с подстановочным знаком, вы выполняете сканирование. Если вы не можете сузить критерии поиска, включив в них первый символ (что может оказаться невыполнимым), вам придется прибегнуть к полнотекстовому поиску.

Вам действительно нужно начинать с подстановочного знака? Почему? Часто вы можете заставить пользователей вводить хотя бы первый символ. Я говорю об этом, потому что некоторые разработчики просто используют подстановочный знак как привычку, а не потому, что есть требование. В большинстве случаев пользователи смогут ввести первый символ, если только в поле не хранятся длинные строки (например, официальные названия аэропортов). В противном случае вам действительно нужно использовать полнотекстовое индексирование, хотя трюк KM с обратным довольно крут, если вам не нужен подстановочный знак в конце.

Если вы можете избежать убийств производительности, сделайте это.

Хотя я согласен с принятым ответом, что полнотекстовое индексирование было бы лучшим решением, и я ни в коем случае не выступаю за использование ведущих поисковых запросов с подстановочными знаками, если они должны выполняться, есть есть потенциальные шаги, которые можно предпринять. чтобы сделать их работу менее плохой.

Кален Делани в книге «Microsoft SQL Server 2008 Internals< /а>" говорит:

как насчет

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

or

CONTAINS(field1 + field2 + field3, @DESC)

Я попробовал одно из возможных решений. До этого решения даже запрос не возвращал результат и вызывал ошибку тайм-аута соединения.

В моем запросе был фильтр даты и другие критерии. Все остальные критерии были похожи на поиск. Одно ключевое слово столбца искало как «% abc%» в столбце ntext, и оно выполняло полное сканирование таблицы.

Решение:

Разделите запрос на 2 части. 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

Это сработало для меня.

Если вы не можете использовать полнотекстовый поиск, вы можете увеличить скорость в 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