locked
SaveChanges very slow RRS feed

  • General discussion

  • Hi guys,

    I'm using EF 6, Sql Server 2012 and VS 2012. My EF model has more than 100 entities.

    I need to reset user choices at a certain point and to do that I bulk delete all user records from a table and recreate them.

    Getting the user records and removing them from the EF memory model is quick but context.SaveChanges() takes way too long. It takes about 2 minutes to delete 1300 records from the database. Something is wrong here. It shouldn't be so slow. There is not a first compilation issue since subsequent invocations take some amount of time to complete.

    Here are the details of my problems:

    Tables:

    A (AK, AField 1, ...)

    B (BKey, BField1, ...)

    UserChoices(AK, BK, Choice, ....). (AK, BK) is composite key in UserChoices table. AK is also a foreign key to A table and BK is a foreign key to B table.

    Table A has about 10 millions records, B about 8000 and UserChoices about 400k.

    So, issue is: do some joins and find about 1300 records to delete from UserChoices. Run DeleteAll method:

                public virtual void DeleteAll(IEnumerable<TEntity> choices)
                {
                    foreach (var choice in choices)
                    {
                        var entry = _context.Entry(choice);
                        entry.State = EntityState.Deleted;
                        _dbset.Remove(choice);
                    }
                }

    Where

    DbContext _context;
    IDbSet<TEntity> _dbset;

    And then do a _context.SaveChanges().

    This SaveChanges takes about 2 minutes to run for these 1300 records.

    Same thing happens when adding records.

    Any ideas of how to fix this problem? I did set AutoDetectChangesEnabled to false and I also tried removing the foreign keys. None worked.

    • Changed type Fred Bao Monday, December 30, 2013 7:23 AM
    Wednesday, December 11, 2013 10:10 PM

All replies

  • Hello,

    >> It takes about 2 minutes to delete 1300 records from the database

    Is the 2 minutes spent on context.SaveChange() only or the whole program?

    If we want to delete the entities, we just use codes like below:

    var entry = _context.Entry(choice);
    
    entry.State = EntityState.Deleted;
    

    Or

    _dbset.Remove(choice);

    Any of them is ok. Have a try to use just one to see whether it will be better. And have a try to use the IQuerable<T> rather than IEnumerable<T>, because IQuerable<T> will translate the method like Take() and Skip() to sql statement, but ToList<T> and IEnumerable<T> will firstly load all data into memory, then they do the filter in memory.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 12, 2013 9:45 AM
  • Thank you for your answer.

    I did make the suggested changes (IQueryable instead of IEnumerable and remove the entry.State statement) but that didn't help.

    Those 2 minutes are spent by SaveChanges() operation. DeleteAll() executes in 1 second and SaveChanges() in 2 minutes.

    Thursday, December 12, 2013 2:40 PM
  • What is the configuration of your database server?

    Is is far away?

    Can you profile this activity on the server? 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, December 12, 2013 2:59 PM