One Database with 20 million records or 51 databases with 50,000-300,000 records in each database?
database, model-view-controller, sql-server
Solution
Create a single database, where you put all those records in. But, do it in a structured fashion offcourse.
For instance, you could create a table 'State', and a table called 'Business'. Create a relationship between those 2 tables. Normalize your database further.
When you want to have a performant database, it starts by defining a good, normalized DB schema. Add the necessary indexes, and you should be fine.
A database is designed to be able to handle a large amount of records.
Problem
I've bought a CSV United States business database with ~20 million records, which is divided to 51 databases, every database represents a state. I need to write an ASP.NET MVC Web Application that will query this database, by state and more arguments. Should I create a SQL Server database and import all the records in the all 51 csv files? Or maybe should I query directly to the csv files? What will be fastest? Feel free to suggest and other solutions. Thanks.