Lucene.NET with SQL SERVER 2000

views:

165

answers:

Lucene.NET with SQL SERVER 2000

I have a SQL 2000 database with around 10 million rows and I need to make a query to get the product information based on the full / partial text search. Based on this I need to join back to other tables to check on my business process. I have this implemented using SQL proc, but I can only validate around 6 rows a sec (without threads.. its a long business logic). I am trying to find a better ways to improve performance. Lucene.NET might help on this. I have couple of questions.

Can you point me to right sources.

While building index on Lucene, how would I sync up with the SQL database and lucene DB? Do you think Lucene can give real performance gain?

+1 A:

You can start with Mark Krellenstein's 'Search Engine versus DBMS', to see whether a full text search engine, such as Lucene, is the solution for you. In theory, Lucene should be faster than SQL for textual search, but your mileage may vary.
You can do incremental updates with Lucene, which are a bit similar to database replication. This keeps the Lucene index synchronized with the database.

Yuval F 2009-09-08 06:28:03

Thanks for your answer. I looked through quite a few tutorials and am at the point where I can index records of DB to the local file.As I said my DB is of around 10 million records, Lucene takes a while if I build the index from scratch. My approach is : --Crate a windows service which looks for any update in database (each hour) and keeps the index in sync with database records.Say, there are 2000 records added / hour, is that going to adversely affect updating the index. Does the search slow down during index build?

bkhanal 2009-09-08 14:00:21

Well, you need to somehow get the records into the Lucene index in the first place. The best way for this is to build the index from scratch offline. Once you have that, you can use incremental updates like you suggest. I only have experience in Java Lucene with MySQL, but I believe the issues are similar in your setting - 2000 records per hour, or about 40 per minute, seems reasonable for updates. It may slow down the search. See http://www.lucidimagination.com/Community/Hear-from-the-Experts/Articles/Scaling-Lucene-and-Solr

Yuval F 2009-09-09 07:50:02

Here is an article on using LINQ to Lucene to work with SQL. This may point you in the right direction.

Bernesto 2009-09-30 06:40:58

ansaurus

tags:

views:

answers:

Lucene.NET with SQL SERVER 2000

related questions