1

I am having a table containing a column of type VARCHAR. I want to search strings inside the column according to user input query. I want to implement Approximate Searching. And my table contains Lacs of records. There are some ways I am thinking I can implement searching.

  1. Load All records in C# and apply searching algorithm on it. (But it will consume too much memory.)

  2. Fetch records individually or in some predefined batch size and apply searching algorithm on it. (But it will establish database connection rapidly, which may downgrade the performance.)

I am sure that, there will be some other mechanism to implement this functionality or some technique to store data so that i can search it faster.

Can anybody give me any better idea, to implement this?

Rumit Parakhiya
  • 2,674
  • 3
  • 24
  • 33
  • 3
    Sounds like you need to look into full-text search: http://msdn.microsoft.com/en-us/library/ms142571.aspx – Tao Aug 09 '11 at 09:06
  • If you write a database function you can use that function in your SQL. Nowadays most databases have languages able to code a fuzzy search. – Marino Šimić Aug 09 '11 at 09:08
  • @Tao: Why don't you make that comment into an answer? You'll most likely get some rep for it. – LukeH Aug 09 '11 at 09:15
  • @LukeH: fair enough, I didn't want to take the time to write a proper answer, but I guess i accidentally "claimed" the answer in my comment. – Tao Aug 09 '11 at 09:28
  • Hmm, I assumed SQL Server, but I just realized this is not specified; @Coder, can you confirm / retag? – Tao Aug 09 '11 at 09:37
  • @Tao: yeah, I am trying to implement it in SQL server only. And thanks for the link posting. It is really interesting. I'll try to use it if it can do the thing.... – Rumit Parakhiya Aug 10 '11 at 04:47

2 Answers2

3

Lucene is one of the best ways to search. You can still store your string in the database, but build a Lucene index out of it and then use it to search.

Petar Ivanov
  • 91,536
  • 11
  • 82
  • 95
  • Might be nice to indicate how one might go about it, this question seems to link to a tutorial/overview: http://stackoverflow.com/questions/37059/lucene-net-and-sql-server/37173#37173 – Tao Aug 09 '11 at 09:32
  • @Tao: Thank for the reference Tao, really nice tutorial. – Rumit Parakhiya Aug 10 '11 at 04:58
2

SQL Server has built-in functionality to do exactly what you're looking to do, it's called Full-Text Search.

Overview from Microsoft here: http://msdn.microsoft.com/en-us/library/ms142571.aspx

The general concept is that you tell SQL Server what tables/columns contain searchable text, and it builds space-efficient and query efficient "full-text indexes"; these indexes are built asynchronously (so your updates/inserts are not slowed down), and since SQL Server 2005 they are stored with your database (eg in backups), so they're easily managed.

When you want to search, the query language is different from "normal" text matching.

Full-Text search is even available in the free "SQL Server 2008 Express with Advanced Services" edition, so cost is no longer a concern.

Tao
  • 13,457
  • 7
  • 65
  • 76