SQL Server and performance for dynamic searches

Question

I was wondering what were the best practices for making a query in sql with a dynamic value, lets say i have a Value(nvarchar(max))

value: "912345678"

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

value: "Michael"

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

value: "Street number 10"

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

This approuches are a bit slow since searching for a number that has 9 digits would be faster without % like this

select * from AllData
where Number like '912345678' 

I use a EDMX to make a connection to an external database in C#, like this:

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();

How can i increase performance?

Answer 1

Wildcard searches like these on varchar/nvarchar fields are going to to iterate over every character, more or less, for records that meet the critieria.

A great (and fast!) option for these kinds of searches is to:

1. Make a fulltext catalog to store fulltext indexes.
2. Put a fulltext index on the columns in each table you need to search.
3. Use the CONTAINS keyword when you search rather than wildcards.

You mentioned looking for credible sources, here is a good read.

Answer 2

If using LIKE and PATINDEX didn't get you needed performance then you probably should write sp which will use FTS.

Answer 3

To search with 'like' in EF you can use Contains():

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

But with this kind of search you will never achieve good performance. You need to change the way you search or store the data in the DB. For example if you are sure that the user was looking for name, you can search only in 'name' column.