快速背景：使用 NHibernate 的代碼庫，我已從 .NET Framework 4.7.2 升級到 .NET 6。
我注意到一個特定的查詢在 .NET Framework 專案中需要大約 40 毫秒，但在 .NET Core/5/6 專案中最多需要 6 秒。SSMS 中的相同查詢幾乎是即時的。
在 .NET Framework 和 .NET 6 之間，生成的查詢本身（如 SQL Profiler 中所示）和連接字串都是相同的。甚至試圖通過在查詢之前復制 SSMS 中的所有 SET（ARITHABORT 等）來重現減速，但沒有任何區別。閱讀有關查找 .NET 查詢和 SSMS 查詢之間差異的所有不同技巧和提示（非常深入的主題：https ://www.sommarskog.se/query-plan-mysteries.html ）。也就是說，這絕對不是查詢計劃或引數嗅探的問題，也不是應用程式查詢性能和 SSMS 查詢性能之間差異的任何“典型”罪魁禍首。
提煉問題導致我創建 .NET Framework 和 .NET 6 控制臺專案做完全相同的事情。.NET Framework 中仍為 40 毫秒，.NET 6 中為 6 秒。最終發現，當涉及長度未定義的列時，異步讀取查詢結果是一個問題（https://github.com/dotnet/SqlClient/issues/593）。果然，當我在 .NET 6 專案中使用同步方法時，查詢時間下降到 50ms。
好的，所以不要使用異步。但是如何讓 NHibernate 不使用 async 以便它產生的查詢再次執行？
uj5u.com熱心網友回復：
因此，經過數小時的調查，我發現了一些讓我想出這個解決方法的難題。
- 在一個稍微不同的問題上，我必須讓 NHibernate 使用
Microsoft.Data.SqlClient（而不是System.Data.SqlClient），這樣做是這樣的（使用FluentNhibernate） - 在 NHibernate 源代碼中四處尋找，我發現
DbCommandWrapper - 一篇SO 帖子描述了設定如何
CommandBehavior.SequentialAccess加快異步讀取。
在這和插入之間Microsoft.Data.SqlClient，我想我會擴展MicrosoftDataSqlClientDriver，覆寫該CreateCommand()方法以回傳一個包裝，通過覆寫DbCommand手動設定CommandBehavior為。這沒有用；它拋出一個例外： 所以資料被亂序讀取，導致問題。SequentialAccessExecuteDbDataReader(behavior)
Attempt to read from column ordinal '21' is not valid. With CommandBehavior.SequentialAccess, you may only read from column ordinal '24' or greater. - 另一篇 SO 帖子描述了 EntityFramework Core 遇到了同樣的問題，其中一個答案引起了我的注意： . 用于按順序讀取列并將資料存盤在緩沖區中
的包裝器。DbDataReader
所以讓我們新建一個SqlClient驅動，把創建的命令包裝起來，這樣我們就可以包裝閱讀器，這樣閱讀器就可以順序訪問執行，并按順序讀取資料，并將其存盤在緩沖區中。
public class MicrosoftDataSqlClientSyncDriver : MicrosoftDataSqlClientDriver
{
public override DbCommand CreateCommand()
{
var cmd = base.CreateCommand();
return new DbCommandWrapper(cmd);
}
private class DbCommandWrapper : DbCommand
{
public readonly DbCommand Command;
public DbCommandWrapper(DbCommand cmd)
{
Command = cmd;
}
public override string CommandText { get => Command.CommandText; set => Command.CommandText = value; }
public override int CommandTimeout { get => Command.CommandTimeout; set => Command.CommandTimeout = value; }
public override CommandType CommandType { get => Command.CommandType; set => Command.CommandType = value; }
public override bool DesignTimeVisible { get => Command.DesignTimeVisible; set => Command.DesignTimeVisible = value; }
public override UpdateRowSource UpdatedRowSource { get => Command.UpdatedRowSource; set => Command.UpdatedRowSource = value; }
protected override DbConnection DbConnection { get => Command.Connection; set => Command.Connection = value; }
protected override DbParameterCollection DbParameterCollection => Command.Parameters;
protected override DbTransaction DbTransaction { get => Command.Transaction; set => Command.Transaction = value; }
public override void Cancel()
{
Command.Cancel();
}
public override int ExecuteNonQuery()
{
return Command.ExecuteNonQuery();
}
public override object ExecuteScalar()
{
return Command.ExecuteScalar();
}
public override void Prepare()
{
Command.Prepare();
}
protected override DbParameter CreateDbParameter()
{
return Command.CreateParameter();
}
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
{
var reader = Command.ExecuteReader(CommandBehavior.SequentialAccess);
return new DbDataReaderWrapper(reader);
}
protected override void Dispose(bool disposing)
{
if (disposing)
Command.Dispose();
base.Dispose(disposing);
}
public override async ValueTask DisposeAsync()
{
await Command.DisposeAsync();
await base.DisposeAsync();
}
}
private class DbDataReaderWrapper : DbDataReader
{
readonly DbDataReader Reader;
private object[] Cache;
public DbDataReaderWrapper(DbDataReader reader)
{
Reader = reader;
Cache = new object[] { };
}
private T Get<T>(int ordinal)
{
if (Cache[ordinal] != DBNull.Value) return (T)Cache[ordinal];
return default(T);
}
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 NotImplementedException();
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 NotImplementedException();
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);
if (Reader.Read())
{
Cache = new object[Reader.FieldCount];
for (int i = 0; i < Reader.FieldCount; i )
{
if (Reader.IsDBNull(i))
Cache[i] = DBNull.Value;
else
Cache[i] = Reader.GetValue(i);
}
return true;
}
return false;
}
public override void Close() => Reader.Close();
public override async Task CloseAsync() => await Reader.CloseAsync().ConfigureAwait(false);
public override DataTable GetSchemaTable() => Reader.GetSchemaTable();
public override async Task<DataTable> GetSchemaTableAsync(CancellationToken cancellationToken = default) => await Reader.GetSchemaTableAsync(cancellationToken).ConfigureAwait(false);
public override async Task<ReadOnlyCollection<DbColumn>> GetColumnSchemaAsync(CancellationToken cancellationToken = default) => await Reader.GetColumnSchemaAsync(cancellationToken).ConfigureAwait(false);
public override async Task<bool> NextResultAsync(CancellationToken cancellationToken) => await Reader.NextResultAsync(cancellationToken).ConfigureAwait(false);
public override async Task<bool> ReadAsync(CancellationToken cancellationToken)
{
Array.Clear(Cache);
if (await Reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
Cache = new object[FieldCount];
for (int i = 0; i < FieldCount; i )
{
if (await Reader.IsDBNullAsync(i))
Cache[i] = DBNull.Value;
else
Cache[i] = Reader.GetValue(i);
}
return true;
}
return false;
}
protected override void Dispose(bool disposing)
{
Reader.Dispose();
base.Dispose(disposing);
}
public override async ValueTask DisposeAsync()
{
await Reader.DisposeAsync().ConfigureAwait(false);
await base.DisposeAsync();
}
}
}
到目前為止，我已經避免了特定問題查詢的巨大減速，并且我還沒有遇到任何其他問題。希望這種解決方法是可以接受的，至少在 .NET 人員最終弄清楚之前是這樣。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/519871.html