Massive performance difference using direct database access using ADO.NET with Sequential Access

[mrlund]

Question

I don't know if this is a known issue, but I just encountered a performance fix with such as massive impact that I felt I had to report it.

I was using EF normally to fetch a single row from a single table. The table has a field where I store json data, and hence for some extreme situations, that field can get quite large (in one case up to 10mb). Most of the time they're much smaller, less than 500kb, so I didn't hit this before.

My original code took about 45 seconds (!) to return that single (admittedly large) record. When I rewrote it using ADO.NET and Sequential Access that same query returned in ~200ms, or a 180x performance improvement!

Original code:

await _context.Sessions
          .Include(x => x.CaseSnapshot)
          .Where(x => x.SessionId == new Guid(sessionId))
          .Select(x => x.CaseSnapshot)
          .FirstOrDefaultAsync(cancellationToken);

New code:

  using (var connection = _context.Database.GetDbConnection())
  {
      await connection.OpenAsync(cancellationToken);

      using (var command = connection.CreateCommand())
      {
          command.CommandText = @"
  SELECT cs.CaseModel
  FROM Sessions s
  INNER JOIN CaseSnapshots cs ON s.CaseSnapshotId = cs.Id
  WHERE s.SessionId = @sessionId";

          var parameter = command.CreateParameter();
          parameter.ParameterName = "@sessionId";
          parameter.Value = sessionIdGuid;
          command.Parameters.Add(parameter);

          using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
          {
              if (await reader.ReadAsync(cancellationToken))
              {
                  var bufferSize = 81920; // 80KB buffer
                  var buffer = new char[bufferSize];
                  long dataIndex = 0;
                  StringBuilder caseModelBuilder = new StringBuilder();

                  while (true)
                  {
                      var bytesRead = reader.GetChars(0, dataIndex, buffer, 0, bufferSize);
                      if (bytesRead == 0)
                          break;

                      caseModelBuilder.Append(buffer, 0, (int)bytesRead);
                      dataIndex += bytesRead;
                  }

                  return caseModelBuilder.ToString();
                  // Use caseModel as needed
              }
          }
      }
  }

Your code

Stack traces

Verbose output

EF Core version

8

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8

Operating system

Windows 11 and SQL Azure

IDE

No response

[roji]

SqlClient has well-known performance issues when using async I/O - this is unrelated to EF Core.