Optimizing performance when searching on multiple columns with a single search string



  • 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 joe.smith@email.com 93567902
    George Smith gsmith2643@gmail.com 47684765
    George Jones jones.george@gjinc.com 85730724
    DECLARE 
        @search VarChar(100)
    

    SET @search = '%George Smith%'
    /*
    SET @search = '%George%'
    SET @search = '%Smith%'
    SET @search = '%gsmith2643@gmail.com%'
    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



  • I would try to avoid OR operators in this scenario. I actually mention https://brendan-mccaffrey.com/2021/12/20/stop-using-the-or-operator/ 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2