What's the difference between using SaveChanges () only once after adding data through a foreach and using inside the foreach?

4

I'm inserting an average of 50,000 records into the database via Entity Framework. And I had a question regarding the following code placements: in the first (1) placement, I use foreach creating objects with their data and adding them ( contexto.addToTabela ), using SaveChanges() outside foreach at the end of the whole process, being used only once. The second (2) placement would be using the SaveChanges() within the foreach, that is, with each added object there is a commit . What is the difference between the two situations?

Sample code:

Situation 1:

using (entities contexto = new entities())
{
    foreach (var item in lista)
    {
        ...
        contexto.AddToTabela(item);
    }

    contexto.SaveChanges();
}

Situation 2:

using (entities contexto = new entities())
{
    foreach (var item in lista)
    {
        ...
        contexto.AddToTabela(item);
        contexto.SaveChanges();
    }
}
    
asked by anonymous 30.06.2014 / 17:00

1 answer

6

In logic: In situation 1 you are putting everything into a single transaction, if a line fails, the rollback will be about everything. Already in situation 2, it will save up the line that fails, without a specific try catch handling it will stop the foreach.

In performance: In situation 1 it will stack everything in memory, the 50,000 record and send at one time. Depending on the case, it may make the process a bit slow. In situation 2 it receives the data and already sends, there are many connections with the DB server. Fixing a third situation where it does a SaveChanges every 100 items can perform better than both situations. It does not weigh much memory and also does not make as many unique requests with DB.

If you can use Entity Framework 6, use the AddRange method: link

    
30.06.2014 / 18:13