实体框架异步操作需要十倍的时间来完成

因为几天前我得到了这个问题的链接，所以我决定发布一个小更新。我能够复制的结果的 原始答案使用，目前，最新版本的 EF (6.4.0)和。NET Framework 4.7.2.令人惊讶的是，这个问题从来没有得到改进。

.NET Framework 4.7.2 | EF 6.4.0 (Values in ms. Average of 10 runs)


non async : 3016
async : 20415
ExecuteReaderAsync SequentialAccess : 2780
ExecuteReaderAsync Default : 21061
ExecuteReader SequentialAccess : 3467
ExecuteReader Default : 3074

这就引出了一个问题: 在 dotnet 核心是否有改进？

我从一个新的 dotnet Core 3.1.3项目的原始答案中复制了代码，并添加了 EF Core 3.1.3。结果是:

dotnet core 3.1.3 | EF Core 3.1.3 (Values in ms. Average of 10 runs)


non async : 2780
async : 6563
ExecuteReaderAsync SequentialAccess : 2593
ExecuteReaderAsync Default : 6679
ExecuteReader SequentialAccess : 2668
ExecuteReader Default : 2315

令人惊讶的是，有很多改善。因为线程池被调用，所以似乎仍然存在一些时间延迟，但是它的速度比。NET 框架的实现。

我希望这个答案可以帮助其他人在未来得到这种方式。

有一个解决方案，允许使用异步而不牺牲性能，测试与 EF 核心和 MS SQL 数据库。

首先，你需要为 DBDataReader做一个包装:

1. 它的 ReadAsync方法应该读取整个行，将每个列的值存储在一个缓冲区中。
2. 它的 GetXyz方法应该从前面提到的缓冲区中获取值。
3. 可选地，使用 GetBytes + Encoding.GetString代替 GetString。对于我的用例(每行16KB 的文本列) ，它导致了同步和异步的显著加速。
4. 可选地，调整连接字符串的 Packet Size。对于我的用例，值32767导致了同步和异步的显著加速。

你现在可以制作一个 DbCommandInterceptor，拦截 ReaderExecutingAsync来制作一个带有循序存取的 DBDataReader，用上面提到的包装纸包装。

EF Core 将尝试以非顺序的方式访问字段——这就是为什么包装器必须首先读取并缓冲整行的原因。

下面是一个实现示例(拦截异步和同步) :

/// <summary>
/// This interceptor optimizes a <see cref="Microsoft.EntityFrameworkCore.DbContext"/> for
/// accessing large columns (text, ntext, varchar(max) and nvarchar(max)). It enables the
/// <see cref="CommandBehavior.SequentialAccess"/> option and uses an optimized method
/// for converting large text columns into <see cref="string"/> objects.
/// </summary>
public class ExampleDbCommandInterceptor : DbCommandInterceptor
{
public async override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
var behavior = CommandBehavior.SequentialAccess;


var reader = await command.ExecuteReaderAsync(behavior, cancellationToken).ConfigureAwait(false);


var wrapper = await DbDataReaderOptimizedWrapper.CreateAsync(reader, cancellationToken).ConfigureAwait(false);


return InterceptionResult<DbDataReader>.SuppressWithResult(wrapper);
}


public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
var behavior = CommandBehavior.SequentialAccess;


var reader = command.ExecuteReader(behavior);


var wrapper = DbDataReaderOptimizedWrapper.Create(reader);


return InterceptionResult<DbDataReader>.SuppressWithResult(wrapper);
}


/// <summary>
/// This wrapper caches the values of accessed columns of each row, allowing non-sequential access
/// even when <see cref="CommandBehavior.SequentialAccess"/> is specified. It enables using this option it with EF Core.
/// In addition, it provides an optimized method for reading text, ntext, varchar(max) and nvarchar(max) columns.
/// All in all, it speeds up database operations reading from large text columns.
/// </summary>
sealed class DbDataReaderOptimizedWrapper : DbDataReader
{
readonly DbDataReader reader;
readonly DbColumn[] schema;


readonly object[] cache;
readonly Func<object>[] materializers;


[System.Runtime.CompilerServices.MethodImpl(System.Runtime.CompilerServices.MethodImplOptions.AggressiveInlining)]
private T Get<T>(int ordinal)
{
if (cache[ordinal] != DBNull.Value) return (T)cache[ordinal];


return (T)(object)null; // this line will throw an exception if T is not a reference type (class), otherwise it will return null
}


private DbDataReaderOptimizedWrapper(DbDataReader reader, IEnumerable<DbColumn> schema)
{
this.reader = reader;
this.schema = schema.OrderBy(x => x.ColumnOrdinal).ToArray();


cache = new object[this.schema.Length];




byte[] stringGetterBuffer = null;


string stringGetter(int i)
{
var dbColumn = this.schema[i];


// Using GetBytes instead of GetString is much faster, but only works for text, ntext, varchar(max) and nvarchar(max)
if (dbColumn.ColumnSize < int.MaxValue) return reader.GetString(i);


if (stringGetterBuffer == null) stringGetterBuffer = new byte[32 * 1024];


var totalRead = 0;


while (true)
{
var offset = totalRead;


totalRead += (int)reader.GetBytes(i, offset, stringGetterBuffer, offset, stringGetterBuffer.Length - offset);


if (totalRead < stringGetterBuffer.Length) break;


const int maxBufferSize = int.MaxValue / 2;


if (stringGetterBuffer.Length >= maxBufferSize)


throw new OutOfMemoryException($"{nameof(DbDataReaderOptimizedWrapper)}.{nameof(GetString)} cannot load column '{GetName(i)}' because it contains a string longer than {maxBufferSize} bytes.");


Array.Resize(ref stringGetterBuffer, 2 * stringGetterBuffer.Length);
}


var c = dbColumn.DataTypeName[0];


var encoding = (c is 'N' or 'n') ? Encoding.Unicode : Encoding.ASCII;


return encoding.GetString(stringGetterBuffer.AsSpan(0, totalRead));
}


var dict = new Dictionary<Type, Func<DbColumn, int, Func<object>>>
{
[typeof(bool)] = (column, index) => () => reader.GetBoolean(index),
[typeof(byte)] = (column, index) => () => reader.GetByte(index),
[typeof(char)] = (column, index) => () => reader.GetChar(index),


[typeof(short)] = (column, index) => () => reader.GetInt16(index),
[typeof(int)] = (column, index) => () => reader.GetInt32(index),
[typeof(long)] = (column, index) => () => reader.GetInt64(index),


[typeof(float)] = (column, index) => () => reader.GetFloat(index),
[typeof(double)] = (column, index) => () => reader.GetDouble(index),
[typeof(decimal)] = (column, index) => () => reader.GetDecimal(index),


[typeof(DateTime)] = (column, index) => () => reader.GetDateTime(index),
[typeof(Guid)] = (column, index) => () => reader.GetGuid(index),


[typeof(string)] = (column, index) => () => stringGetter(index),
};


materializers = schema.Select((column, index) => dict[column.DataType](column, index)).ToArray();
}


public static DbDataReaderOptimizedWrapper Create(DbDataReader reader)


=> new DbDataReaderOptimizedWrapper(reader, reader.GetColumnSchema());


public static async ValueTask<DbDataReaderOptimizedWrapper> CreateAsync(DbDataReader reader, CancellationToken cancellationToken)
            

=> new DbDataReaderOptimizedWrapper(reader, await reader.GetColumnSchemaAsync(cancellationToken).ConfigureAwait(false));


protected override void Dispose(bool disposing) => reader.Dispose();


public async override ValueTask DisposeAsync() => await reader.DisposeAsync().ConfigureAwait(false);




public override object this[int ordinal] => Get<object>(ordinal);
public override object this[string name] => Get<object>(GetOrdinal(name));


public override int Depth => reader.Depth;


public override int FieldCount => reader.FieldCount;


public override bool HasRows => reader.HasRows;


public override bool IsClosed => reader.IsClosed;


public override int RecordsAffected => reader.RecordsAffected;


public override int VisibleFieldCount => reader.VisibleFieldCount;




public override bool GetBoolean(int ordinal) => Get<bool>(ordinal);


public override byte GetByte(int ordinal) => Get<byte>(ordinal);


public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) => throw new NotSupportedException();


public override char GetChar(int ordinal) => Get<char>(ordinal);


public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) => throw new NotSupportedException();


public override string GetDataTypeName(int ordinal) => reader.GetDataTypeName(ordinal);


public override DateTime GetDateTime(int ordinal) => Get<DateTime>(ordinal);


public override decimal GetDecimal(int ordinal) => Get<decimal>(ordinal);


public override double GetDouble(int ordinal) => Get<double>(ordinal);


public override IEnumerator GetEnumerator() => reader.GetEnumerator();


public override Type GetFieldType(int ordinal) => reader.GetFieldType(ordinal);


public override float GetFloat(int ordinal) => Get<float>(ordinal);


public override Guid GetGuid(int ordinal) => Get<Guid>(ordinal);


public override short GetInt16(int ordinal) => Get<short>(ordinal);


public override int GetInt32(int ordinal) => Get<int>(ordinal);


public override long GetInt64(int ordinal) => Get<long>(ordinal);


public override string GetName(int ordinal) => reader.GetName(ordinal);


public override int GetOrdinal(string name) => reader.GetOrdinal(name);


public override string GetString(int ordinal) => Get<string>(ordinal);


public override object GetValue(int ordinal) => Get<object>(ordinal);


public override int GetValues(object[] values)
{
var min = Math.Min(cache.Length, values.Length);


Array.Copy(cache, values, min);


return min;
}


public override bool IsDBNull(int ordinal) => Convert.IsDBNull(cache[ordinal]);


public override bool NextResult() => reader.NextResult();


public override bool Read()
{
Array.Clear(cache, 0, cache.Length);


if (reader.Read())
{
for (int i = 0; i < cache.Length; ++i)
{
if ((schema[i].AllowDBNull ?? true) && reader.IsDBNull(i))
                        

cache[i] = DBNull.Value;


else cache[i] = materializers[i]();
}


return true;
}


return false;
}


public override void Close() => reader.Close();


public async override Task CloseAsync() => await reader.CloseAsync().ConfigureAwait(false);


public override DataTable GetSchemaTable() => reader.GetSchemaTable();


public async override Task<DataTable> GetSchemaTableAsync(CancellationToken cancellationToken = default) => await reader.GetSchemaTableAsync(cancellationToken).ConfigureAwait(false);


public async override Task<ReadOnlyCollection<DbColumn>> GetColumnSchemaAsync(CancellationToken cancellationToken = default) => await reader.GetColumnSchemaAsync(cancellationToken).ConfigureAwait(false);


public async override Task<bool> NextResultAsync(CancellationToken cancellationToken) => await reader.NextResultAsync(cancellationToken).ConfigureAwait(false);


public async override Task<bool> ReadAsync(CancellationToken cancellationToken)
{
Array.Clear(cache, 0, cache.Length);


if (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
for (int i = 0; i < cache.Length; ++i)
{
if ((schema[i].AllowDBNull ?? true) && await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false))
                        

cache[i] = DBNull.Value;


else cache[i] = materializers[i]();
}


return true;
}


return false;
}
}
}

我现在不能提供一个基准，希望有人会在评论中这样做。

增加@rducom 给出的答案。这个问题在 Microsoft.EntityFrameworkCore 6.0.0中仍然存在

阻塞部分实际上是 SqlClient,@AndriySvyryd 在 EF 核心项目中推荐的解决方案是:

不要使用 VARCHAR (MAX)或不要使用异步查询。

在使用 async查询读取大型 JSON 对象和 Image (二进制)数据时，我遇到了这种情况。

相关网址:

Https://github.com/dotnet/efcore/issues/18571#issuecomment-545992812

Https://github.com/dotnet/efcore/issues/18571

Https://github.com/dotnet/efcore/issues/885

Https://github.com/dotnet/sqlclient/issues/245

Https://github.com/dotnet/sqlclient/issues/593

对我来说，快速修复是将调用包装在任务中，而只是使用同步方法。

它不是一个通用的解决方案，但是对于小的聚合，它可以局限于应用程序的一小部分。