SQL Server и производительность для динамического поиска

Мне было интересно, какие были лучшие методы для создания запроса в sql с динамическим значением, скажем, у меня есть значение (nvarchar (max))

Значение: "912345678" 

select * from AllData
where Number like '%912345678%'

значение: "Майкл" 

select * from AllData
where Name like '%Michael%'

Значение: "Улица номер 10" 

select * from AllData
where Address like '%Street number 10%'

Этот подход немного медленнее, так как поиск числа, имеющего 9 цифр, будет быстрее без%, как этот

select * from AllData
where Number like '912345678'

Я использую EDMX для подключения к внешней базе данных на С#, например:

var Result = EDMXEntity.Entities.Where(x => 
(SqlFunctions.PatIndex("%" + Value.ToLower() +"%", x.Name.ToString().ToLower()) > 0) 
|| (SqlFunctions.PatIndex("%" + Value.ToLower() +"%", x.Number.ToString().ToLower()) > 0)
|| (SqlFunctions.PatIndex("%" + Value.ToLower() +"%", x.Address.ToString().ToLower()) > 0)).Take(50).ToList();

Как повысить производительность?

Ответы

Ответ 1

Подстановочные запросы, подобные этим в полях varchar/nvarchar, собираются перебирать каждый символ, более или менее, для записей, соответствующих критериям.

Отличная (и быстрая!) опция для таких видов поиска:

  • Создайте полнотекстовый каталог для хранения полнотекстовых индексов.
  • Поместите полнотекстовый индекс в столбцы в каждой таблице, которые нужно искать.
  • Используйте ключевое слово CONTAINS при поиске, а не в виде подстановочных знаков.

Вы упомянули о том, что искали достоверные источники, здесь является хорошим показанием.

Ответ 2

Если использование LIKE и PATINDEX не принесло вам необходимой производительности, вы, вероятно, должны написать sp, который будет использовать FTS.

Ответ 3

Для поиска с помощью "like" в EF вы можете использовать Содержит():

var Result = EDMXEntity.Entities.Where(
x => x.Name.Contains(Value) ||
x => x.Number.ToString().Contains(Value) ||
x => x.Address.Contains(Value)).Take(50).ToList();

Но при таком поиске вы никогда не добьетесь хорошей производительности. Вам нужно изменить способ поиска или хранения данных в БД. Например, если вы уверены, что пользователь ищет имя, вы можете искать только в столбце "name".