Way of searching 30,000 SQL Records

I am about to make a simple search facility on my website, where a user will enter around 2-4 keywords which will get searched in two columns in a table in my MS SQL database. One column is a varchar (50) called title and one column is a varchar(2500) called description. There will be about 20,000-30,000 records potentially at any one time to search.

The keywords will need to return "the best matches" - you know the kind you get on search pages like ebay that return the closest matches. The way I was thinking of doing this is seems kind of naive - I thought I can read all 30,000 records of the table into and object like this:

public class SearchableObject
{
    string Title {get; set;}
    string Description {get; set;}
    int MatchedWords {get; set;}
}

Then create a List of that object e.g List go through all 30,000 records, populate the List, find out the ones that match most times and return the top 10 using something like

 if Description.contains(keyword1);

But then find out how many times it occurs in the string to populate the MatchedWords field.

My question is, is this the best way to do this? If not, what would be开发者_运维知识库?

full-text index search will do the trick.

http://msdn.microsoft.com/en-us/library/ms142547.aspx

You should use a full text indexing solution. MS SQL Server 7 and later has a full text indexing engine built in (here's a decent overview article). You could also consider using external products such as Lucene (available for Java and C#/.NET).

i think you only want to use C# to parse the search parameters, not actually perform the searching and aggregation... So no, it's not really the best way. Use SQL Server to do the search heavy-lifting.

take a look at lucene for .net, that will allow full index of your text.

http://incubator.apache.org/lucene.net/

the .net developers on this site may be able to tell you if there are any better alternatives

If you're working with Java or C#, I'd recommend Lucene or Lucene.NET respectively.

Use a full-text search engine such as Lucene. There exists also a .NET version.