Entity Framework 6 - Memory and performance issues when working with Binary data (async method)

Question

We have a old legacy system at work that stores binary data in our SQL Server. When we use our new .NET platform that are using EF6 to find data in the table with FindAsync, it spikes in memory and the performance is terrible compared to Find (the sync method)

Does anyone know a workaround or a way to fix this issue? I have created an issue with Microsoft already,mbut we are in a hurry since our customer are experiencing a 20 minute freeze on the server. The reason for the freeze seems to be that the memory doesn't get released fast enough for the garbage collection to clean the memory, and other scenarios where the server is too busy to clean up fast enough.

I have made a quick .NET 4.8 project with EF 6 to reproduce the error.

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();
            }
        }
    }
}

Performance issue

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

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 issue

• 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

(We found the same issue using EF Core)

Answer 1

To use EF with tables with very large blobs you should use Table Splitting, and only fetch the entity with the blob as needed.

Or you can avoid ever loading the blob data into memory by removing the byte[] entity property, and replacing it with a streaming access method on your DbContext like this:

public Stream GetItemData(int Id)
{
    var con = (SqlConnection)Database.Connection;

    if (con.State != System.Data.ConnectionState.Open)
        con.Open();

    var cmd = con.CreateCommand();
    cmd.CommandText = "select data from Items where id = @id";
    cmd.Parameters.Add(new SqlParameter("@id", Id));

    var rdr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
    var stream = rdr.GetStream(0);
    return stream;            
}