What is the most efficient way to search text on a big table?

Question

I need to search a text field (varchar 500) on a table with about 150,000 rows.

The first option I tried was executing a stored procedure directly on the db server which looks something like:

Select p.ProductID, 
    p.ProductDescription, 
    p.SalesPrice 
From Products p 
Where p.ProductDescription Like '%' + @PartialDescription + '%'

The result was pretty slow. Even with index on ProductDescription, performance was not fast enough.

The next solution I came up with works in following steps:

1. Read the whole Products table into List of Type Product and cache it.

2. Everytime I need to make a search, I go through the collection and do the string comparision as below

   List<Product> searchResultItems = new List<Product>();
   for (int i = 0; i < cachedProducts.Count; i++)
   {
       Product p = cachedProducts[i];
   
       if (p.Description.IndexOf(partialDescription, StringComparison.OrdinalIgnoreCase)>=0)
       {
           searchResultItems.Add(p);
       }
   }
   return searchResultItems;
   

This solution is little faster than searching it directly into the database. By caching the table in memory, it avoids database calls to a large extent. However, its still slow in comparision to the legacy system (built more than 10 years ago on FileMaker). I do not have access to the source code of legacy system and do not know much about it.

With MS SQL Server 2008 database, code written on C# and cache utilizing ObjectCache on System.Runtime.Caching, I was expecting my solution to easily outperform the legacy system. Embarrassingly, its not the case. How can I optimize my search method? What am I doing wrong? Above function resides in a WCF Service and is consumed (basicHttpBinding) by a web application without any fancy controls. What can I do to make it faster?

Searching directly into the database, can it be a better solution with any kind of optimizations? How can I optimize it?

Answer 1

If you are using MS SQL 2008, I would suggest use Full Text Search. Enable FT Index on your table, and then follow the FTS hints to search the text.