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?

Your all conditions are same. `SqlFunctions.PatIndex("%" + Example.ToLower() +"%", x.Name.ToString().ToLower()) > 0)` — Amit, Jun 19 '15 at 11:20
`select * from AllData where Number like '912345678' ` equals to `select * from AllData where Number = '912345678' ` so it will be always faster. — Max, Jun 19 '15 at 11:23
@Max doesn't matter. the optimizer will treat them both the same. — Zohar Peled, Jun 19 '15 at 11:25
The database can't use any indexes if you use a wildcard prefix. You'll have to use FTS — Panagiotis Kanavos, Jun 22 '15 at 13:04

Chris Schubert · Accepted Answer · 2015-06-22T12:48:52.827

13

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:

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

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

edited Jun 22 '15 at 12:48

answered Jun 22 '15 at 11:14

Chris Schubert

1,288
8
17

1

If you want to search for a string within a string, you ARE doing a fulltext search. Given what you laid out so far, the correct thing to do is a standard implementation of a fulltext search. – jerrylagrou Jun 23 '15 at 16:58

score 2 · Answer 2 · answered Jun 19 '15 at 11:35

2

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

answered Jun 19 '15 at 11:35

Max

1,784
2
19
26

score 2 · Answer 3 · answered Jun 24 '15 at 17:11

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.

SQL Server and performance for dynamic searches

3 Answers3