Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We have a repository of tables. Around 200 tables, each table can be thousands of rows, all tables are originally in Excel sheets.

Each table has a different scheme. All data is text or numbers.

We would like to create an application that allows free text search on all tables (we define which columns will be searched in each table) efficiently - speed is important.

The main dilemma is which DB technology we should choose.

We created a mock up by importing all tables to MS SQL Server, and creating a full text index over them. The search is done using the CONTAINS keyword. This solution works well for a small number of tables, but it doesn't scale.

We thought about a NoSQL solution, but we don't yet have any experience in it.

Our limitations (which unfortunately I can not effect): Windows servers only. But we can install on them whatever we want.

share|improve this question
5  
200 tables with "thousands" of rows? Keep all of it in memory and don't bother with a database (or use the DB only as persistent storage that you read from on startup). Seriously! That's not a lot of data these days. –  Joachim Sauer May 16 '12 at 8:12
    
Sauer is right. Thousands of rows over 200 tables is manageable with memory provided the text in tables are small (say some 2000 characters per data point). If that's the case you may Redis or MongoDB. MongoDB is preferred since you can get started with the Windows executable straight away. –  Ubermensch May 16 '12 at 9:04
    
@JoachimSauer: post your comment as answer and I guess you will get lots of upvotes. –  Doc Brown May 16 '12 at 10:45
    
@KellyG: the interesting question here is: may the data change while someone uses your application? Perhaps in a transactional manner? And if so, do changes have to be done on a central server, while your application runs in parallel on different clients and the clients shall always have access to the newest revision of your data? If so, then a database may be the right thing to choose though your amount of data is small. On the other hand, if the change of the data is decoupled from the usage of the data, go with Joachim's suggestion. –  Doc Brown May 16 '12 at 10:51
    
Thank you for all the comments. The data is going to change manually in bulks every few weeks. So if we go on the in-memory solution, should we build our own searching mechanism or should we use something that already exists? MongoDB seems to provide only in-memory caching. –  KellyG May 16 '12 at 11:04

1 Answer 1

up vote 1 down vote accepted

If you were to go the NoSQL route, or even if you weren't, I'd suggest looking at Lucene (also has a .NET implementation) for your indexing needs. It is a very high performance component and would actually yawn at the numbers you're quoting.

If it's a little low level for you, look at RavenDb. It's a NoSQL solution that uses Lucene for its indexing. It's a .NET solution though (you never mentioned which language/platform you're using), if you are using .NET it really shines with its support for LINQ. I would consider another alternative (like MongoDB) if you're not using .NET because there aren't currently any RavenDB clients implemented for other platforms.

I would say try them both if you have the time (it sounds like you're still in the prototyping stage of your application). There are advantages to each. As I mentioned, RavenDB really shines for a .NET application because of how deeply ingrained LINQ is in it's API. You define indexes using LINQ, you query with LINQ...it's really powerful stuff. MongoDB is older and can be used easily with other platforms if you have that need.

Hope this helps.

share|improve this answer
    
MongoDB does have a .NET driver –  Ubermensch May 16 '12 at 12:21
    
yeah we're using .NET. Should we try RavenDb or MongoDB? –  KellyG May 16 '12 at 12:22
    
@Ubermensch I wasn't saying that MongoDB didn't have a .NET driver. Pardon if that was the implied message, it wasn't my intention –  Mike Brown May 16 '12 at 12:26
    
@MikeBrown The last part of the statement made me think that way since AFAIK, MongoDB does have the their own drivers for most number of languages. –  Ubermensch May 16 '12 at 12:30
    
@KellyG see my edit –  Mike Brown May 16 '12 at 12:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.