CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 2233

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?

Upvotes: 2

Views: 1008

Answers (6)

iammichael
iammichael

Reputation: 9757

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).

Upvotes: 5

Juha Syrjälä
Juha Syrjälä

Reputation: 34271

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

Upvotes: 1

Bryan Menard
Bryan Menard

Reputation: 13364

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

Upvotes: 1

Karl
Karl

Reputation: 2945

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

Upvotes: 1

Paul Sasik
Paul Sasik

Reputation: 81429

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.

Upvotes: 2

Henry Gao
Henry Gao

Reputation: 4936

full-text index search will do the trick.

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

Upvotes: 8

Related Questions