0

I have to implement search that finds substring in the name of the user. User has FirstName and LastName in 2 columns. It is good enough to do WHERE FirstName LIKE '%searchText%' OR LastName LIKE '%searchText%'

What is my problem that I want to solve is performance. Let's say that currently I expect like 1000 users tops. I do not want to search to take ages. So I thought of indexes (those columns will not change much, I expect that the value of these columns will almost never change). I know that I will be looking for both columns I need multi column index.

Is this correct way of doing this? Or it is better to use SQL full text search for this (please provide some good link)? Would it be better to create a View where FirstName and LastName will be concatenated and search there? Or it is better to just use e.g. Azure Search (Currently, this is the only and it may be the last entity I would need to search for)?

I am using .NET 4.6 and EntityFramework hosted on Azure web apps.

Thanks

Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
ino_fleg
  • 87
  • 1
  • 7
  • 1
    When you required leading wildcards no amount of indexing is going to help because your query would become nonSARGable. In cases like this full text is usually better. I am voting to close this question as too broad because there just aren't enough details to offer any real advice here. – Sean Lange Sep 12 '17 at 13:45
  • So if I understand correctly by changing query to FirstName% OR LastName%, do I get SARGable query? I would not say that question is too broad. I am asking for the best practice for the given search scenario. I do not need anyone to give me some piece of code or something, just a direction which way should I resolve this search scenario or a similiar one. Anyways Thanks for your response it helped me to look at SARGable stuff. – ino_fleg Sep 12 '17 at 13:54
  • 2
    With 1000 users, just scan them all. If your table is very wide, add an index on (LastName, FirstName) so SQL can scan that instead of the whole table. – David Browne - Microsoft Sep 12 '17 at 13:56

1 Answers1

0

Because of the leading wildcard character, an index will not be used. The only time an index will be used is if the wildcard is either in the middle or at the end of the string. Adding one index on LastName, FirstName can be a good suggestion as well if your table is very wide, like David Browne said.

If you are truly needing to search with both wildcards (i.e. a partial match), then I would take a look at the amount of data in your table. If it's just a few thousand rows we're talking about, a table scan will still be very fine performance wise. If we're talking about something like 50.000 rows or more, then a full text index would be best. This seems like a good tutorial on the matter: https://learn.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search

Steven Lemmens
  • 1,441
  • 3
  • 17
  • 30