One Database with 20 million records or 51 databases with 50,000-300,000 records in each database?

Question

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.

Answer 1

One table, with appropriate indexes. 20 million records is peanuts.

Answer 2

I would import the data into one big database. As long as the table is correctly indexed it will offer better performance when querying as instead of having to scan each file it should be able to use the correct indexes to speed things up.

Answer 3

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.