3

We have a system that imports Waivers that customers fill out from a kiosk or online. Since the data entry is performed by the customer, the input format on fields can be very inconsistent. I want to allow my users to enter a single search string and have the best chance of finding the customer's record. Here is a sample table and query that I have written, along with some sample search strings that a user could enter. My application will add the "%" wildcard characters for the like searches to the user's input. This table currently has about 30K rows, with expected growth of another 20K per year.

  1. Is this a good way to write the query so that a single search string can search across all these columns?
  2. For performance, should I create a separate index on each of these columns? For the FirstName and LastName columns, would a separate index on each column or a multi-column index be better for this type of searching?
FirstName LastName Email DriversLicense
Joe Smith [email protected] 93567902
George Smith [email protected] 47684765
George Jones [email protected] 85730724
DECLARE 
    @search VarChar(100)

SET @search = '%George Smith%'
/*
SET @search = '%George%'
SET @search = '%Smith%'
SET @search = '%[email protected]%'
SET @search = '%47684765%'
*/

SELECT * FROM Waiver w
WHERE 
    w.FirstName like @search or
    w.LastName like @search or
    CONCAT(w.FirstName, ' ', w.LastName) like @search or
    w.Email like @search or
    w.DriversLicense like @search
    

1 Answer 1

3

I would try to avoid OR operators in this scenario. I actually mention Stop using the OR operator in UPDATES in a recent blog post. While that post was about updates, the concept should translate well here. Basically, using the OR operator will make indexing challenging. You'll need to know which column needs to go first in any index you create, and hope it's SELECTIVE and will always have input.

Also, you'll want to avoid the leading '%' in your search string, as no index will be able to help in that case. The easiest way to visualize why no index will help here it to think of a phone book. I know, many people probably don't know what these are any more. Now, picture trying to look up a phone number where you're missing the first letter of someone's last name. If you knew the name started with an 'M', you might just flip to the Ms. Then maybe flip a few more pages if you land on 'Ma', but need 'Mc". In either case, you'll get there fast. But without the first letter, you'll have to look at every record, front to back, of the entire phone book to find a name like '%cCaffrey, for example.

Since your concatenation between first name and last name, you'll probably want to consider an index on a computed column, or an indexed view. You may need to experiment to see which one works best in your scenario. For example, here would be a computed column with an index.

ALTER TABLE Waiver ADD FullName AS CONCAT(FirstName, ' ', LastName)) PERSISTED
GO
CREATE NONCLUSTERED INDEX IX_FullName ON dbo.Waiver (FullName)
GO

If you were to continue concatenate those columns in your WHERE clause, they'll produce a full table scan every time. SQL Server can't know two columns equal your condition until it concatenates them for every row in the table. These issues might not end up being a problem at 30k rows now, but could be big problems as the row counts grow.

If you must using a generic search box, I would try writing your query somewhat like this. In this case, you could create a unique index for each column. You're using SELECT *, so you'll always be doing KEY LOOKUPS anyways.

DECLARE @search VarChar(100)

SET @search = 'Smith'

SELECT *
FROM Waiver
WHERE LastName LIKE @search + '%'

UNION

SELECT *
FROM Waiver
WHERE FullName LIKE @search + '%'

UNION

SELECT *
FROM Waiver
WHERE Email LIKE @search + '%'

UNION
    
SELECT *
FROM Waiver
WHERE DriversLicense LIKE @search + '%'

I removed FirstName from the search in the example, because it would already be met in the FullName search.

You would still need to evaluate additional indexes on common search terms.

With that said, I would advise you to see if can create several search boxes on the webpage, one for each search condition.

2
  • I like this approach, however it won't return a result if the user types in "George Smith", which is a format my users are used to searching for customers by name since our POS system stores the names in a single column such as this. I think I will parse the input and split it on a space, and if I get two words I will assume they are FirstName and LastName and search accordingly. Feb 12, 2022 at 19:48
  • 1
    I have never used persisted computed columns before. This looks like a great solution to a couple issues that I have. Thank you Feb 13, 2022 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.