EntityFramework在执行更新查询时非常缓慢(EntityFramework is painfully slow at executing an update query)

我们正在研究一个性能问题，其中EF 6.1.3速度很慢，我们无法弄清楚可能导致它的原因。

数据库上下文初始化为：

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

我们已将性能问题与以下方法隔离：

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; }

changedProperties列表中有两个名称，EF正确生成了一个更新这两个属性的更新语句。

重复调用此方法（处理数据项的集合），大约需要15-20秒才能完成。

如果我们用以下方法替换上面的方法，执行时间将减少到3-4秒：

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; }

在writer（存储库实现）上调用的UpdatePropertiesAsync方法如下所示：

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）？

We're investigating a performance issue where EF 6.1.3 is being painfully slow, and we cannot figure out what might be causing it.

The database context is initialized with:

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

We have isolated the performance issue to the following method:

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 just these two properties.

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

If we replace the method above with the following, execution time drops 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 called on the writer (a repository implementation) looks like this:

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); } }

What is EF doing that completely kills performance? And is there anything we can do to work around it (short of using another ORM)?

最满意答案

通过对代码进行计时，我能够看到EF花费的额外时间是在将对象附加到上下文的调用中，而不是在实际查询中更新数据库。

通过消除所有对象引用（在附加对象之前将它们设置为null并在更新完成后恢复它们），EF代码以“可比较的时间”（5秒，但具有大量日志记录代码）运行到手写解决方案。

因此看起来EF有一个“错误”（有些人可能称之为功能），导致它以递归方式检查附加对象，即使已禁用更改跟踪和验证。

更新：EF 7似乎通过允许您在调用Attach时传入GraphBehavior枚举来解决此问题。

By timing the code I was able to see that the additional time spent by EF was in the call to Attach the object to the context, and not in the actual query to update the database.

By eliminating all object references (setting them to null before attaching the object and restoring them after the update is complete) the EF code runs in "comparable times" (5 seconds, but with lots of logging code) to the hand-written solution.

So it looks like EF has a "bug" (some might call it a feature) causing it to inspect the attached object recursively even though change tracking and validation have been disabled.

Update: EF 7 appears to have addressed this issue by allowing you to pass in a GraphBehavior enum when calling Attach.

更多推荐