CODEWITHSUNDEEP
sql-serverazurefull-text-searchazure-sql-databasefull-text-indexing
Luke Merrett
Luke Merrett

Reputation: 5824

Partial Searching Two Text Fields on SQL Azure - Best Practice

I am trying to ascertain the best way of implementing a partial search on two columns within a table. The aim is to have this search perform as quickly as possible.

Our issue is that our database is hosted on SQL Azure; which does not support FullTextIndexing. This means the only native commands available to us in SQL are CHARINDEX() and LIKE '% %'.

The structure of the query if we were to do it in pure T-SQL would be:

DECLARE @SearchTerm VarChar(255) = 'Luke'

SELECT  AU.UserID,
        AU.FirstName,
        AU.Surname
FROM dbo.Users AU
WHERE AU.FirstName LIKE '%'+@SearchTerm+'%'
OR AU.Surname LIKE '%'+@SearchTerm+'%'

Also available to us is the ability to use Lucene; we already have it set up on a Worker Role on Windows Azure however we would have to maintain the integrity of the data both inside the database and within Lucene.

What I want to find out is:

  1. Is there a better way of performing a LIKE search in T-SQL than what I am using above
  2. If I added a Calculated Column to the table containing both the first and last names would this improve the performance of the query?
  3. Alternatively; if we move to Lucene; would the read performance be that much greater than the above query? (In regards to this; there is under 10,000 rows currently in the dbo.Users table)
  4. Throwing the doors open; is there some method we haven't considered that would make this a whole load easier?

Upvotes: 1

Views: 413

Answers (1)

Mark Leighton Fisher
Mark Leighton Fisher

Reputation: 5693

Adding a calculated column containing both first and last names will force the results to contain both the first and the last name, but your SQL above is for matching either the first OR the last name.

If you want to match first AND last name, a calculated column may be faster as there are tricks the database programmer can apply for you (example: Boyer-Moore fast string searching, which gets faster as the pattern size increases).

My experience with Lucene is that it is significantly faster than any database search -- I've seen nothing faster on everyday hardware. But as you say, you will have to keep the Lucene index in sync with the database.

Upvotes: 2

Related Questions