CODEWITHSUNDEEP
sqlsql-serverfull-text-search
JNB
JNB

Reputation: 428

Efficient wildcard string search over multiple columns in a table

I need to be able to return some rows based on a wildcard string search across multiple columns.

I've come up with 3 ways of achieving this:

DECLARE @searchString VARCHAR(max) = '%Foo%';
-- Approach 1
SELECT  * FROM AddressFile
WHERE (ClientRef LIKE @searchString
       OR HouseNumber LIKE @searchString
       OR Street LIKE @searchString
       OR Area LIKE @searchString
       OR Town LIKE @searchString
       OR PostCode LIKE @searchString)

-- Approach 2
SELECT  * FROM AddressFile
WHERE (
  (CONCAT(ClientRef, HouseNumber, Street,  Area, Town, PostCode)) LIKE @searchString
)

-- Approach 3
SELECT * FROM AddressFile
WHERE(
  AddressFile.AddressAggregate LIKE @searchString
  -- AddressAggregate = PersistedComputed : CONCAT(ClientRef, HouseNumber, Street,  Area, Town, PostCode)
)

Approach 3 gives better performance, approach 1 and 2 are roughly equivalent with 1 taking the edge.

Are there better (faster) methods?

Is there a problem with approach 3? Generally the columns used to compute AddressAggregate will not change after the initial insert, but it will certainly happen in a small percentage of cases ~5-10%.

Upvotes: 2

Views: 1672

Answers (1)

glasko
glasko

Reputation: 141

You should look into using Full Text Search. One of the issues with your wildcard search is that the query will not be sargable — since it's looking for the substring in the middle of your strings, it will need to scan the entire index rather than seek to a particular range of values. Depending on the number of rows in your table, you could see a performance benefit from using the Full Text Search feature of SQL Server over traditional indexes.

Upvotes: 2

Related Questions