Save and query huge amount of data in SQL Server

Question

I work on a project in a Financial Institution. In this company databases are distributed in separate branches. We want to set up a data center and turn all databases to one database. But in this situation we have a database table with more than 100 million records. I think SQL operations (ex insert, update, select) in this table will be too slow and costly. Which scenarios can help me? We use code first approach of Entity Framework in our project.

Proper indexing, hand-tuned queries - it's absolutely doable - not easy, but doable — marc_s, Aug 27 '14 at 16:26
You could set up a data warehouse and bulk load using SSIS at appropriate intervals. — Dan Bracuk, Aug 27 '14 at 16:32
Also look into partitioning. [Partitioned Tables and Indexes](http://msdn.microsoft.com/en-us/library/ms190787.aspx) — Wagner DosAnjos, Aug 27 '14 at 16:33
Any professional commercial SQL DBMS product can handle this with proper hardware and a qualified DBA and/or DB Developer. — RBarryYoung, Aug 28 '14 at 21:12

score 1 · Accepted Answer · edited May 23 '17 at 12:27

A) 100 million is not too much for SQL server. With appropriate indexes, disk topology, memory and CPU allocations plus a good DBA to oversee for a while. Things should be fine.

b) Initial migration is NOT an EF topic. I would not recommend EF for that task. EF can Create the DB, but use tools to load data. Sample SO post

c) Test and/or do some research on expected insert/Select times on the SQL server with 100 million rows.

d) The trick to getting good performance with EF is holding as FEW records in a context as possible. Good EF code first code is the key to it working well.

score 0 · Answer 2 · answered Aug 27 '14 at 16:42

Take a look on Bulk and BCP commands. They are used to copy large amount of data.

http://technet.microsoft.com/en-us/library/ms130809(v=sql.110).aspx

http://technet.microsoft.com/en-us/library/ms190923(v=sql.105).aspx

If you don't use MS SQL Server, look for the correspondent feature in your server.

Note that 100 milion records may not be really large amount of data. I recommend you do some performance test to realize if it will really be an issue.

Save and query huge amount of data in SQL Server

2 Answers2