EF Core - Memory and performance issues with async methods

[DanielO398]

Yesterday I created a issue in EF6 about a performance issue the company I work for have. We have an old legacy system are putting binary data into the SQL Server. Our new .NET server needs to use this data in some cases and this causes one of our customers to have their server freezing up to 20 minutes when is busy.

I am unsure what the procedure is for issues found in both frameworks. Because the reason I actually found it was to see if it was a possible workaround for us. However is not a viable solution as it will take a lot longer than a weekend to re-write from EF6 to EF Core and the same issue is found in EF Core.

Another discovery I made is that the memory issue is a lot worse in EF Core.

Steps to reproduce

You can find my repository where i recreated the issue from production in a dummy project for both EF Core and EF6. Repo link

Or find the code below for EF Core

using System;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace PerformanceIssueEFCoreAsync
{
  public class Item
  {
    public int Id { get; set; }
    public byte[] Data { get; set; }
  }
  public class ItemContext : DbContext
  {
    public DbSet<Item> Items { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseSqlServer(
@"Data Source=localhost;Initial Catalog=ItemDb;Integrated Security=true;");
    }
  }
  internal class Program
  {
    private static async Task Main(string[] args)
    {
      Console.WriteLine("Ready to consume a lot of memory with EF.");

      using (var db = new ItemContext())
      {
        db.Database.EnsureCreated();

        //insert dummy record
        if (db.Items.ToArray().Length == 0)
        {
          db.Items.Add(new Item { Data = new byte[2 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[20 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[40 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[60 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[80 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[100 * 1024 * 1024] });
          db.Items.Add(new Item { Data = new byte[200 * 1024 * 1024] });
          await db.SaveChangesAsync();
        }
      }
      // Find
      for (int i = 1; i < 8; i++)
      {
        // Find sync - No performance issues
        using (var db = new ItemContext())
        {
          var stopwatch = Stopwatch.StartNew();
          Console.WriteLine("Find sync method doesn't have performance and memory issue");
          Item item = db.Items.Find(i);
          Console.WriteLine($"Record with id '{item.Id}' was fetched in {stopwatch.ElapsedMilliseconds}ms. Press any key to read again...");
        }

        // Find async - performance issues
        using (var db = new ItemContext())
        {
          var stopwatch = Stopwatch.StartNew();
          Console.WriteLine("Reproduce FindAsync performance and memory issue:");
          Item item = await db.Items.FindAsync(i);
          Console.WriteLine($"Record with id '{item.Id}' was fetched in {stopwatch.ElapsedMilliseconds}ms. Press any key to read again...");
        }
      }

      using (var db = new ItemContext())
      {
        db.Database.EnsureDeleted();
      }
    }
  }
}

Performance data
Below image shows the performance details found in the calls between Find() and FindAsync()

The id's have the following binary sizes

ID 1 = 2mb
ID 2 = 20mb
ID 3 = 40mb
ID 4 = 60mb
ID 5 = 80mb
ID 6 = 100mb
ID 7 = 200mb

We also found the following memory usage differences
Below images shows the memory 200mb Find()

However with FindAsync() we get a lot more usage
Below images shows the memory 200mb FindAsync()

But the memory issue is also in EF 6 however not as bad as EF Core
Below images shows the memory 200mb FindAsync()

Further technical details

EF Core version: 3.1.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10 - 1909 (Build 18363.836)
IDE: Visual Studio 2019 - 16.4.1

[ErikEJ]

What prevents you from simply using Find iso FindAsync?

[roji]

This is unrelated to EF Core - it seems like a SqlClient issue. When running your scenario via BenchmarkDotNet on Sqlite (see code below), everything works fine:

BenchmarkDotNet=v0.12.0, OS=ubuntu 20.04
Intel Xeon W-2133 CPU 3.60GHz, 1 CPU, 12 logical and 6 physical cores
.NET Core SDK=5.0.100-preview.6.20266.3
  [Host]     : .NET Core 3.1.1 (CoreCLR 4.700.19.60701, CoreFX 4.700.19.60801), X64 RyuJIT
  DefaultJob : .NET Core 3.1.1 (CoreCLR 4.700.19.60701, CoreFX 4.700.19.60801), X64 RyuJIT

Method	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
Find	663.4 ms	5.65 ms	5.01 ms	-	-	-	502.15 MB
FindAsync	665.1 ms	3.60 ms	3.36 ms	-	-	-	502.15 MB

This seems very similar to dotnet/SqlClient#245, but that issue was closed in SqlClient 1.1, and I can confirm that running the benchmark with SqlClient 1.1.3 still seems to produce the problematic perf behavior. I'll investigate further exactly what triggers this.

BenchmarkDotNet benchmark

[MemoryDiagnoser]
public class Program
{
    [GlobalSetup]
    public async Task Setup()
    {
        await using var ctx = new ItemContext();
        await ctx.Database.EnsureDeletedAsync();
        await ctx.Database.EnsureCreatedAsync();

        ctx.Items.AddRange(
            new Item { Data = new byte[2 * 1024 * 1024] },
            new Item { Data = new byte[20 * 1024 * 1024] },
            new Item { Data = new byte[40 * 1024 * 1024] },
            new Item { Data = new byte[60 * 1024 * 1024] },
            new Item { Data = new byte[80 * 1024 * 1024] },
            new Item { Data = new byte[100 * 1024 * 1024] },
            new Item { Data = new byte[200 * 1024 * 1024] });
         await ctx.SaveChangesAsync();
    }

    [Benchmark]
    public void Find()
    {
        using var ctx = new ItemContext();
        for (var i = 1; i < 8; i++)
        {
            var _ = ctx.Items.Find(i);
        }
    }

    [Benchmark]
    public async Task FindAsync()
    {
        await using var ctx = new ItemContext();
        for (var i = 1; i < 8; i++)
        {
            var _ = await ctx.Items.FindAsync(i);
        }
    }

    static void Main(string[] args)
        => BenchmarkRunner.Run<Program>();
}

public class ItemContext : DbContext
{
    public DbSet<Item> Items { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlite("Filename=/tmp/foo.sqlite");
            //.UseSqlServer(@"...");
}

public class Item
{
    public int Id { get; set; }
    public byte[] Data { get; set; }
}

[roji]

Opened dotnet/SqlClient#593 with a SqlClient repro (no EF involved). I'd currently advise against using asynchronous APIs with SqlClient when dealing with large data.