EntityFramework is very slow when executing update request

Question

We study the performance issue when EF 6.1.3 is painfully slower and we cannot figure out what might cause it.

The database context is initialized with:

Configuration.ProxyCreationEnabled = false;
Configuration.AutoDetectChangesEnabled = false;
Configuration.ValidateOnSaveEnabled = false;

We identified the performance issue as follows:

protected virtual async Task<long> UpdateEntityInStoreAsync(T entity,
                                                            string[] changedProperties)
{
    using (var session = sessionFactory.CreateReadWriteSession(false, false))
    {
        var writer = session.Writer<T>();
        writer.Attach(entity);
        await writer.UpdatePropertyAsync(entity, changedProperties.ToArray()).ConfigureAwait(false);
    }
    return entity.Id;
}

There are two names in the changedProperties list, and EF correctly generated an update statement that updates only these two properties.

This method is called repeatedly (to process a set of data elements) and takes about 15-20 seconds.

If we replace the above method, the execution time will be reduced to 3-4 seconds:

protected virtual async Task<long> UpdateEntityInStoreAsync(T entity,
                                                            string[] changedProperties)
{
    var sql = $"update {entity.TypeName()}s set";
    var separator = false;
    foreach (var property in changedProperties)
    {
         sql += (separator ? ", " : " ") + property + " = @" + property;
         separator = true;
    }
    sql += " where id = @Id";
    var parameters = (from parameter in changedProperties.Concat(new[] { "Id" })
                      let property = entity.GetProperty(parameter)
                      select ContextManager.CreateSqlParameter(parameter, property.GetValue(entity))).ToArray();
    using (var session = sessionFactory.CreateReadWriteSession(false, false))
    {
        await session.UnderlyingDatabase.ExecuteSqlCommandAsync(sql, parameters).ConfigureAwait(false);
    }
    return entity.Id;
}

The UpdatePropertiesAsync method invoked by the author (repository implementation) is as follows:

public virtual async Task UpdatePropertyAsync(T entity, string[] changedPropertyNames, bool save = true)
{
    if (changedPropertyNames == null || changedPropertyNames.Length == 0)
    {
        return;
    }

    Array.ForEach(changedPropertyNames, name => context.Entry(entity).Property(name).IsModified = true);
    if (save)
        await context.SaveChangesAsync().ConfigureAwait(false);
    }
}

EF, ? -, , ( ORM)?

Answer 1

, , EF, " ", .

( null , ), EF " " (5 , ) .

, , EF "" ( ), , .

: EF 7, , , GraphBehavior Attach.

Answer 2

Entity , SaveChanges() insert , Entity.

2 db-, db- - insert , - select, .

, numOfRecords * 2 * .

context.Database.Log = message => Debug.WriteLine(message);, sql , , .

BulkInsert, : https://efbulkinsert.codeplex.com/

Answer 3

, :

Configuration.AutoDetectChangesEnabled = false;
Configuration.ValidateOnSaveEnabled = false;

, , .

, foreach, ( , ).

, , , SaveChanges(); SaveChangesAsync();, , , .

In addition, if you still do not see further success, try to get rid of the context message SaveChanges();, and then create a new one, depending on the size of the list of your entities, clearing the context can lead to even greater improvements.

But all this depends on the number of objects that we are talking about, and can be noticeable only in hundreds and thousands of recording scenarios.