1

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.

Improve this question
6
  • 6
    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
    Commented May 16, 2012 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
    Commented May 16, 2012 at 9:04
  • @JoachimSauer: post your comment as answer and I guess you will get lots of upvotes.
    – Doc Brown
    Commented May 16, 2012 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
    Commented May 16, 2012 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
    Commented May 16, 2012 at 11:04

1 Answer 1

Reset to default
2

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.

Improve this answer
5
  • MongoDB does have a .NET driver
    – Ubermensch
    Commented May 16, 2012 at 12:21
  • yeah we're using .NET. Should we try RavenDb or MongoDB?
    – KellyG
    Commented May 16, 2012 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
    – Michael Brown
    Commented May 16, 2012 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
    Commented May 16, 2012 at 12:30
  • @KellyG see my edit
    – Michael Brown
    Commented May 16, 2012 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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