CODEWITHSUNDEEP
sql-serverdatabasemodel-view-controller
Alon Gubkin
Alon Gubkin

Reputation: 57129

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

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.

Upvotes: 3

Views: 658

Answers (3)

Frederik Gheysels
Frederik Gheysels

Reputation: 56944

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.

Upvotes: 5

Kevin Ross
Kevin Ross

Reputation: 7215

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.

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799024

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

Upvotes: 5

Related Questions