Entityframework - Performance and Memory issue when using async methods such as FindAsync

[DanielO398]

The company I work for have a old legacy system that stores data into our MSSQL database. When our server written in DotNet full framework have to find data in the database using EF6 it performance terribly. One of our customers servers are freezing up to 20 minutes and it seems to be because of the garbage collector cannot clean up fast enough. Further more the performance between FindAsync and Find are huge, and is clear that only FindAsync are having this performance issue.

To make this issue easier to understand, I have managed to reproduce the performance issues and somewhat the memory issue in a dummy project by using binary data.

(Extra note, the issue can be found both in EF6 and EF Core)

Steps to reproduce

The code below can be ran simply by adding connection to a MSSQL database.

using System;
using System.Data.Entity;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

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

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

        public ItemContext() : base(@"Data Source=localhost;Initial Catalog=ItemDb;Integrated Security=False;User ID=user;Password=pw")
        { }
    }

    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.CreateIfNotExists();

                //insert dummy record
                if (db.Items.ToArray().Length == 0)
                {
                    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] });
                    await db.SaveChangesAsync();
                }
            }
            // Find
            for (int i = 1; i < 6; 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");
                    var 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:");
                    var 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.Delete();
            }
        }
    }
}

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

Performance issue
What we can clearly see is that find without running the async method, it takes between 150 to 350ms, but async are taking between 13000ms to 280000ms

Memory

With 2 mb binary data Find uses about 52 mb
With 2 mb binary data FindAsync uses about 96 mb
With 20 mb binary data Find uses about 63 mb
With 20 mb binary data FindAsync uses about 432 mb

Further technical details

EF version: 6.4.4
Database Provider: EntityFramework.SqlServer
Operating system: Windows 10 - 1909 (Build 18363.836)
IDE: Visual Studio 2019 - 16.4.1

[ajcvickers]

This issue has been closed because EF6 is no longer being actively developed. We are instead focusing on stability of the codebase, which means we will only make changes to address security issues. See the repo README for more information.