EF SaveChanges running too slow

Question

I'm trying to insert about 2000-5000 records with 60 fields with EF6 MVC.

This is my code :

using (var db = new MyEntities())
{
    using (var transaction = db.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
    {
        try
        {
            db.Database.CommandTimeout = 180;

            // ToInsertStatement is an ExtensionMethod
            // which generates a sql INSERT statement from list of entities
            // in *N* batch size.
            // INSERT INTO tbl values(),()*N*(); INSERT INTO...
            db.Database.ExecuteSqlCommand(entitiesList.ToInsertStatement(20));

            // Some other changes
            db.SaveChanges();
            transaction.Commit();
        }
        catch (Exception exception)
        {
            transaction.Rollback();
        }
    }
}

According to Admir's answer, I send every 20 records with one INSERT statement and fortunately ExecuteSqlCommand takes only 6 seconds. BUT SaveChanges() takes about 2 minutes!

How can I overcome this problem?

I took a look at [Admir's answer](https://stackoverflow.com/a/15632717/2624611). I changed batch size(values count in INSERT i mean) to 100 and it takes just 20 secs! With 50 batch size,it takes only 9 secs! (with 2500 records). And same as Admir, I don't know WHY?! O_o — ABS, Nov 19 '17 at 16:34
Finally, I found best rows count in an INSERT is about 20. (It's depending to columns count. Read [this article](https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/) — ABS, Nov 19 '17 at 18:00

EF SaveChanges running too slow

0 Answers0