Entity Framework 6 SaveChanges Extremely Slow

0

I am making an application in WPF, and I use Entity Framework v6.1.3 as ORM, database sql server 2012 as a database.

The query is simple. Make an insert of 8 thousand records approximately, it takes me more than 15 minutes. What really takes that amount of time is when you run SaveChanges.

My repository class is as follows.

public class RelevamientosRepository:ABMRepository
    {
        public void AddModels(List<Relevamiento> relevamientos)
        {
            using (EnsayosContext _context = new EnsayosContext())
            {
                _context.Configuration.AutoDetectChangesEnabled = false;

                foreach (Relevamiento relevamiento in relevamientos)
                {           
                    relevamiento.TipoRelevamiento = null;
                    relevamiento.Parcela = null;
                    _context.Entry(relevamiento).State = (relevamiento.EsNuevo) ? EntityState.Added : EntityState.Modified;
                }

                _context.SaveChanges();
            }
        }
    }

In turn in the context I have an override of the savechanges, to save date and time of insert or modification.

public override int SaveChanges()
        {
            foreach (var history in this.ChangeTracker.Entries()
                .Where(e => e.Entity is IModificationHistory && (e.State== EntityState.Added || e.State== EntityState.Modified))
                .Select(e => e.Entity as IModificationHistory))
            {
                history.DateModified = DateTime.Now;
                if (history.DateCreated == DateTime.MinValue)
                {
                    history.DateCreated = DateTime.Now;
                }
            }

            int result = base.SaveChanges();

            return result;
        }

How to optimize data recording?

    
asked by user2119992 02.02.2017 в 17:46
source

2 answers

0

It may be that the SaveChanges override is doing too much work. You could use an alternative. Triggers in SQL Server for example. But what is true is that EF for bulk operations is not a good way.

With SqlBulkCopy I made similar inserts of 10,000 records and 30 fields, and it took no more than a few seconds to complete the operation, it is also a good alternative and it will be VERY quick compared to EF.

You could try converting that List<Relevamiento> to DataTable ( Link ) And then use the SqlBulkCopy to send that information to the target table. It will save you a lot of time since everything is done in memory.

    
answered by 08.02.2017 / 16:22
source
0

You can try disabling some validations like context.Configuration.AutoDetectChangesEnabled = false; or context.Configuration.ValidateOnSaveEnabled = false;

In any case, EF is slow by nature for the "BulkOperations". I would recommend, if you have to update a high number of records, that you would use for example SqlBulkCopy . There are also external classes for EF that try to solve this, for example the Entity Framework Extensions

    
answered by 02.02.2017 в 18:23