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

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
– JMK, Commented Nov 18, 2013 at 15:17
I'd guess that you shouldn't SaveChanges every single time. Maybe do it in 1k chunks? — Tim S.
– Tim S., Commented Nov 18, 2013 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
– ojek, Commented Nov 18, 2013 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
– rekire, Commented Nov 18, 2013 at 15:20

Community · Accepted Answer · 2017-05-23 10:25:38Z

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, 2017 at 10:25

CommunityBot

11 silver badge

answered Nov 18, 2013 at 15:19

Mike Norgate

2,4513 gold badges25 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ojek Over a year ago

I didn't look at it this way, that will surely help, thanks!

Tim S. Over a year ago

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.

D Stanley · Accepted Answer · 2013-11-18 15:18:37Z

3

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.

answered Nov 18, 2013 at 15:18

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related