Quick background: Codebase using NHibernate which I've upgraded from .NET Framework 4.7.2 to .NET 6.
I noticed a particular query that takes about 40 milliseconds in the .NET Framework project but up to 6 seconds in the .NET Core/5/6 project. The same query in SSMS is pretty much instantaneous.
Between .NET Framework and .NET 6, the resulting query itself (as seen in SQL Profiler) and the connection strings are all the same. Even attempted to reproduce the slowdown by copying exactly all the SETs (ARITHABORT, etc) in SSMS before the query, but nothing made a difference. Read about all the different tricks and tips for finding the difference between .NET queries and SSMS queries (very in-depth topic: https://www.sommarskog.se/query-plan-mysteries.html). Which is to say, this was definitely not an issue with query plans or parameter sniffing, or any of the "typical" culprits for differences between application query performance and SSMS query performance.
Distilling the problem led me to create .NET Framework and .NET 6 console projects doing exactly the same thing. Still 40ms in .NET Framework and 6 seconds in .NET 6. Eventually found that it's an issue in Async read of the query results when it involves columns with undefined lengths (https://github.com/dotnet/SqlClient/issues/593). Sure enough, when I used the synchronous methods in the .NET 6 project, the query time went down to 50ms.
Ok, so don't use async. But how do I get NHibernate to not use async so that the query it produces is performant again?
CodePudding user response：
So after many hours of investigation, I've found a couple pieces of the puzzle that lead me to come up with this workaround.
- On a slightly different problem, I had to get NHibernate to use
Microsoft.Data.SqlClient(instead ofSystem.Data.SqlClient), which is done like this (withFluentNhibernate) - Poking around in the NHibernate source code, I found
DbCommandWrapper - One SO post describes how setting
CommandBehavior.SequentialAccessspeeds up the async read.
Between this and plugging inMicrosoft.Data.SqlClient, I figured I'd extendMicrosoftDataSqlClientDriver, override theCreateCommand()method to return a wrappedDbCommandthat manually setsCommandBehaviortoSequentialAccessby overridingExecuteDbDataReader(behavior). This didn't work; it throws an exception:
Attempt to read from column ordinal '21' is not valid. With CommandBehavior.SequentialAccess, you may only read from column ordinal '24' or greater.
So the data is being read out of order, causing issues. - Another SO post describes EntityFramework Core experiences the same issue, and one of the answers caught my eye:.
A wrapper forDbDataReaderthat reads the columns in order and stores the data in a buffer.
So lets create a new SqlClient driver, wrap the created command so that we can wrap the reader, so that the reader can be executed with sequential access and read the data in order, and store it in buffer.
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();
}
}
}
So far, I've avoided the immense slowdown for the particular problem query, and I haven't run into any other issues. Hopefully this workaround is acceptable, at least until the .NET folks figure it all out on their end.