Entity framework - how long should it take to add 75k rows?

Question

I have a following setup:

Table1 - 150k rows currently, need to add another 25k rows,
Table2 - 300k rows, need to add another 50k.

I add rows in a loop, like that (EF code first):

using (var context = new MyDb())
{
  loop() //25k rounds
  {
    var t3 = new Table2();
    var t2 = new Table2();
    var t1 = new Table1{prop1 = t2, prop2 = t3};
    context.Table1.Add(t1);
    context.SaveChanges();
  }
}

So, the loop will take 25k turns in total, and my performance measurement shows me, that it will add those rows in roughly ten hours! And now I have no idea if it is because this is a normal time for this kind of operation with EF, or because there is something wrong with my dbms?

Is this a one time thing? If so EF probably isn't the best choice here. But I'm going to vote to close because how long something like this takes is based on a lot of factors, such as the database type/size/design/whether the tables are indexed etc, I think too many to give any sort of a useful answer. — JMK, Nov 18 '13 at 15:17
I'd guess that you shouldn't `SaveChanges` every single time. Maybe do it in 1k chunks? — Tim S., Nov 18 '13 at 15:18
@JMK: What else could I use? SQL is not the option here due to the complexity of the problem (I obviously shortened it alot in the above example) — ojek, Nov 18 '13 at 15:18
I'm not sure if it would be much simpler to generate that SQL statements and fire them directly to a sql interpreter. You also did not tell us if the db is in productive usage or not. — rekire, Nov 18 '13 at 15:20

score 5 · Accepted Answer · edited May 23 '17 at 10:25

5

Have a look at this answer to a similar question

Fastest Way of Inserting in Entity Framework

The problem is that everytime you go round the loop you are saving the changes to the DB, this results in 25k db writes rather than just one

edited May 23 '17 at 10:25

Community

1
1

answered Nov 18 '13 at 15:19

Mike Norgate

2,393
3
24
45

I didn't look at it this way, that will surely help, thanks! – ojek Nov 18 '13 at 15:22
You should include a summary, quote, relevant code, or example of his code modified to implement this. Not just a link and the short summary you've got. – Tim S. Nov 18 '13 at 15:22

score 3 · Answer 2 · answered Nov 18 '13 at 15:18

EF is not designed for efficient bulk update/insert. I would recommend using another method like BCP (command-line utility to import from text files), SqlBulkCopy form C# (which is basically a .NET wrapper for BCP, or an ETL tool like SSIS.

If you are generating the data in your program (not copying it form another locaiton) then you might try building an INSERT SQL command and using SqlCommand.

Entity framework - how long should it take to add 75k rows?

2 Answers2