EF 6.0 SaveAll() very slow

Question

I'm using EF and saving my POCO objects using this function:

public void SaveAll(IList<CoreEntity> entitaCoreList)
{
    bool result = false;

    using (var context = new NSTEntities())
    {
        //context.Configuration.AutoDetectChangesEnabled = false;
        foreach (var entitaCore in entitaCoreList)
        {
            TRACCIAVEICOLO_T500 tracciamentoVeicoliEF = new TRACCIAVEICOLO_T500();
                           tracciamentoVeicoliEF.C_IDTRACCIAMENTOVEICOLO = tracciaVeicolo.Id;
        CultureInfo ci = CultureInfo.CreateSpecificCulture("en-EN");
        tracciamentoVeicoliEF.Z_COORD = System.Data.Spatial.DbGeography.PointFromText(
            "POINT (" + tracciaVeicolo.Longitudine.ToString(ci) + " " + tracciaVeicolo.Latitudine.ToString(ci) + ")", 4326);
        tracciamentoVeicoliEF.D_DATARILEVAZIONE = tracciaVeicolo.DataRilevazione;
        tracciamentoVeicoliEF.C_CODICEWEBFLEET = tracciaVeicolo.CodiceVeicoloWebfleet;
        tracciamentoVeicoliEF.S_POSITIONSTRING = tracciaVeicolo.posString;
        tracciamentoVeicoliEF.P_TIPOMESSAGGIO = (int) tracciaVeicolo.TipoMessaggio;
        tracciamentoVeicoliEF.V_VELOCITA = tracciaVeicolo.Velocita;
        tracciamentoVeicoliEF.V_DIREZIONE = tracciaVeicolo.Direzione;
        tracciamentoVeicoliEF.S_GPSSTATUS = tracciaVeicolo.GpsStatus;
        tableSet.Add(tracciamentoVeicoliEF);
        }

        context.SaveChanges();
        }
    }
}

But it's very slow, it takes nearly 25 seconds for 1000 records. I tried using a raw query like this:

        public void SaveRaw(List<TracciaVeicolo> v)
        {
            StringBuilder query = new StringBuilder();
query.Append(@"INSERT INTO [dbo].[TRACCIAMENTOVEICOLI_T500]([Z_COORD],[C_CODICEWEBFLEET],[D_DATARILEVAZIONE],[S_POSITIONSTRING],[P_TIPOMESSAGGIO],[V_VELOCITA],[V_DIREZIONE],[S_GPSSTATUS])VALUES ");
            bool first = true;
            foreach(var o in v)
            {
                if (!first)
                {
                    query.Append(",");
                }
                query.AppendFormat("(geography::Point(47.65100, -122.34900, 4326),'{0}','{1}','{2}',{3},{4},{5},'{6}')"
                    , o.CodiceVeicoloWebfleet
                    ,o.DataRilevazione.ToString("yyyy-dd-MM HH:mm:ss")
                ,o.posString
                , (int)o.TipoMessaggio
                , o.Velocita
                , o.Direzione
                , o.GpsStatus);
                first = false;
            }

            using (var context = new NSTEntities())
            {
                context.Database.ExecuteSqlCommand(query.ToString());
            }
        }

And it takes 5 seconds. Am I using EF wrong? I've also tried using context.Configuration.AutoDetectChangesEnabled = false; (as you can see in the first code snippet) but it doesn't change anything

The query EF is running is like this:

declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010CE9297288B82F44404DF4F92823263240)
exec sp_executesql N'insert [dbo].[TRACCIAMENTOVEICOLI_T500]([Z_COORD], [C_CODICEWEBFLEET], [D_DATARILEVAZIONE], [S_POSITIONSTRING], [P_TIPOMESSAGGIO], [V_VELOCITA], [V_DIREZIONE], [S_GPSSTATUS])
values (@0, @1, @2, @3, @4, @5, @6, @7)
select [C_IDTRACCIAMENTOVEICOLO]
from [dbo].[TRACCIAMENTOVEICOLI_T500]
where @@ROWCOUNT > 0 and [C_IDTRACCIAMENTOVEICOLO] = scope_identity()',N'@0 [geography],@1 nvarchar(20),@2 datetime2(7),@3 nvarchar(256),@4 int,@5 float,@6 int,@7 char(1)',@0=@p3,@1=N'1-83645-666EE1173',@2='2016-02-29 15:34:57',@3=N'Vicino a Lecce, 1a Lecce Centro-1B ',@4=0,@5=8,3333333333333339,@6=50,@7='A'

Have you looked in SQL Profiler to see what queries its actually executing? — , Feb 29 '16 at 14:31
@Federico if you turn of change tracking, do you get a performance increase? http://stackoverflow.com/questions/21272763/entity-framework-performance-issue-savechanges-is-very-slow Also check this question: http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework — user1666620, Feb 29 '16 at 14:45
What EF is doing will be 1000 separate inserts, with 1000 separate roundtrips across the network to the database. What your hand-tooled code is doing is one round trip: that's going to make a big difference! EF is just not good at mass data uploads. If you need to upload a load of data and EF's speed is not enough then solutions like yours are whet you'll have to do. — simon at rcl, Feb 29 '16 at 14:51
@simonatrcl look at his code again there is a closing bracket for the foreach before that. Actually there is an extra closing bracket, so who know what the op meant — johnny 5, Feb 29 '16 at 15:02
He's building up a string like (value list), (value list), (value list)..... and sending it off appended to the insert into...values. That looks like 1 round trip to me. — simon at rcl, Feb 29 '16 at 15:05
@simonatrcl I'm talking about the save all function in the first code block, its probably 1000 seperate inserts because there seems to be an extra closing bracket but who know which is the error — johnny 5, Feb 29 '16 at 15:19
Oh, right - sorry. Last I looked (a year or two ago), save one call to SaveChanges() generates individual calls for each object added, updated or deleted. So 1000 db calls in this case. — simon at rcl, Feb 29 '16 at 15:25
Have you checked AddRange? Though its still be slower than raw sql, or other way you can write your own procedure and call in from entityframework. — MKMohanty, Mar 03 '16 at 10:29

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

You can try executing it by combining several operations into one transaction. This will save you a lot of time which goes into network latency when you perform single operations.

using (var context = new NSTEntities())
{
    using (var dbContextTransaction = context.Database.BeginTransaction()) 
    {
        try 
        { 
            [... foreach ... tableSet.Add(...) ...]

            context.SaveChanges(); 

            dbContextTransaction.Commit(); 
        } 
        catch (Exception exception) 
        { 
            dbContextTransaction.Rollback();
            // Log exception (never ignore)
        } 
    }
}

You can also log the SQL-operations to determine what is happening. For example using something like: context.Database.Log = s => Debug.WriteLine(s);

It still is going to do 1000 insert statements, but at least you guarantee that your save is atomic (but I have a suspicion that a `SaveChanges()` is atomic too, but I could be wrong). — Scott Chamberlain, Feb 29 '16 at 14:55

score 0 · Answer 2 · answered Feb 29 '16 at 15:51

As you noticed, Entity Framework SaveChanges is very slow since a database round trip is made for every changes.

They have some best practices you can use like using AddRange instead of "Add" but at the end, you will still have some performance issue because 1000 database round trips will be performed.

Disclaimer: I'm the owner of the project Entity Framework Extensions

One way to dramatically improve performance is by using a third party library which allow to use bulk operations.

public void SaveAll(IList<CoreEntity> entitaCoreList)
{
    bool result = false;

    using (var context = new NSTEntities())
    {
        // ... code ...

        context.BulkSaveChanges();
    }
}

EF 6.0 SaveAll() very slow

2 Answers2