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.
DB technology for efficient search in tabular data?
database;search
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.