EF core 8: Bulk Delete comparison, ExecuteDelete() Vs Remove+SaveChanges()


Date Added (UTC):

20 May 2024 @ 07:51

Date Updated (UTC):

20 May 2024 @ 07:58

.NET Version(s):

.NET 8

Tag(s):

#EntityFramework

Added By:
Profile Image

Kayode Philip    GitHub
Blog   
Nigeria    
.NET developer|Systems engineer|Databases(SQL)

Benchmark Results:




Benchmark Code:


using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Configs;
using BenchmarkDotNet.Jobs;
using BenchmarkDotNet.Running;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace Reisty.TestConsole
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Create a manual configuration
            var config = ManualConfig.Create(DefaultConfig.Instance)
                .AddJob(Job.Default
                    .WithWarmupCount(1)
                    .WithIterationCount(40));
            var result = BenchmarkRunner.Run<DeleteService>(config);
            //DeleteService deleteService = new DeleteService();
            //deleteService.BulkDeleteBySaveChanges();
        }
    }

    [MemoryDiagnoser]
    public class DeleteService
    {

        [Benchmark]
        public void BulkDeleteBySaveChanges()
        {
            DbContextOptionsBuilder<ReistyDbContext> optionsB = new DbContextOptionsBuilder<ReistyDbContext>().UseMySql(connectionString: "server=localhost;database=reisty;user=root;password=", serverVersion: ServerVersion.AutoDetect("server=localhost;database=reisty;user=root;password="));

            ReistyDbContext context = new ReistyDbContext(optionsB.Options);
            IErrorLogs errorlogs = new ErrorlogService(context);
            errorlogs.BulkDeleteBySaveChanges();
        }

        [Benchmark]
        public void BulkDeleteByExecuteDelete()
        {
            DbContextOptionsBuilder<ReistyDbContext> optionsB = new DbContextOptionsBuilder<ReistyDbContext>().UseMySql(connectionString: "server=localhost;database=reisty;user=root;password=", serverVersion: ServerVersion.AutoDetect("server=localhost;database=reisty;user=root;password="));

            ReistyDbContext context = new ReistyDbContext(optionsB.Options);
            IErrorLogs errorlogs = new ErrorlogService(context);
            errorlogs.BulkDeleteByExecuteDelete();
        }

    }
    public interface IErrorLogs
    {
        void BulkDeleteBySaveChanges();
        void BulkDeleteByExecuteDelete();

        ErrorLog Get();
    }

    public class ErrorlogService(ReistyDbContext reistyDbContext) : IErrorLogs
    {
        public void BulkDeleteByExecuteDelete()
        {
            var delete = reistyDbContext.ErrorLogs.Take(8).ExecuteDelete();
        }

        public void BulkDeleteBySaveChanges()
        {
            var errorlogs = reistyDbContext.ErrorLogs.Take(8);
            foreach (var errorlog in errorlogs)
            {
                reistyDbContext.ErrorLogs.Remove(errorlog);
            }
            reistyDbContext.SaveChanges();
        }

        public ErrorLog Get() => reistyDbContext.ErrorLogs.FirstOrDefault();

    }

    public class ReistyDbContext : DbContext
    {
        public ReistyDbContext(DbContextOptions<ReistyDbContext> options) : base(options)
        {
        }
        public virtual DbSet<ErrorLog> ErrorLogs { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }

    public class ErrorLog
    {
        public Guid Id { get; set; }
        public string Message { get; set; }
        public string RequestUri { get; set; }
        public DateOnly DateLogged { get; set; }
        public TimeOnly TimeLogged { get; set; }
    }
}


// .NET 8 Lowered C# Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0234: The type or namespace name 'Options' does not exist in the namespace 'Microsoft.Extensions' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptions<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'ModelBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS1729: 'DbContext' does not contain a constructor that takes 1 arguments
error CS1061: 'ReistyDbContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'ReistyDbContext' could be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0103: The name 'ServerVersion' does not exist in the current context

// .NET 8 IL Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0234: The type or namespace name 'Options' does not exist in the namespace 'Microsoft.Extensions' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptions<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'ModelBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS1729: 'DbContext' does not contain a constructor that takes 1 arguments
error CS1061: 'ReistyDbContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'ReistyDbContext' could be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0103: The name 'ServerVersion' does not exist in the current context

// .NET 8 Jit Asm Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0234: The type or namespace name 'Options' does not exist in the namespace 'Microsoft.Extensions' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptions<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'ModelBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS1729: 'DbContext' does not contain a constructor that takes 1 arguments
error CS1061: 'ReistyDbContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'ReistyDbContext' could be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0103: The name 'ServerVersion' does not exist in the current context


Benchmark Description:

Comparison of Bulk Delete results. Database: MySQL Iterations: 40 each Total Rows deleted: 1000 There is improvement in performance with ExecuteDelete() It runs a single SQL delete query just as the usual which makes it faster: 𝙳𝙴𝙻𝙴𝚃𝙴 𝙵𝚁𝙾𝙼 𝙴𝚛𝚛𝚘𝚛𝙻𝚘𝚐𝚜 𝚆𝙷𝙴𝚁𝙴 𝚒𝚍 𝙸𝙽 ( 𝚂𝙴𝙻𝙴𝙲𝚃 𝚒𝚍 𝙵𝚁𝙾𝙼 ( 𝚂𝙴𝙻𝙴𝙲𝚃 𝚒𝚍 𝙵𝚁𝙾𝙼 𝙴𝚛𝚛𝚘𝚛𝙻𝚘𝚐𝚜 𝙾𝚁𝙳𝙴𝚁 𝙱𝚈 𝚒𝚍 𝙻𝙸𝙼𝙸𝚃 𝟾 ) 𝚊𝚜 𝚜𝚞𝚋𝚚𝚞𝚎𝚛𝚢 ); 𝙍𝙚𝙢𝙤𝙫𝙚-𝙎𝙖𝙫𝙚𝘾𝙝𝙖𝙣𝙜𝙚𝙨 comes out slower. It execute multiple trackable SQL delete script for the iterated EF core Remove(...). And after calls the SaveChanges to save the tracked entities. It's a lot of process which make it inefficient unlike the ExecuteDelete(). When it comes to Bulk delete, 𝐄𝐱𝐞𝐜𝐮𝐭𝐞𝐃𝐞𝐥𝐞𝐭𝐞 is more performant.

The provided benchmark code is designed to compare the performance of two different methods for deleting records in a database using Entity Framework Core (EF Core) in a .NET application. The benchmarks are set up using BenchmarkDotNet, a powerful library for benchmarking .NET code. The configuration specified in the `Main` method sets up the benchmark to run with a single warm-up iteration and 40 actual iterations, aiming to provide a reliable measurement by allowing the JIT compiler to optimize the code before the measurements start and then averaging the results across many runs. ### General Setup - **.NET Version:** The .NET version isn't explicitly mentioned, but the use of EF Core and BenchmarkDotNet suggests that it's targeting .NET Core 3.1 or .NET 5/6, as these are the versions commonly used with these libraries. - **Configuration:** The benchmark is configured to run with 1 warm-up iteration and 40 actual iterations. This setup helps in stabilizing the run times by allowing the JIT (Just-In-Time) compiler to optimize the code before the measurements are taken. - **DbContext Configuration:** The `ReistyDbContext` is configured to connect to a MySQL database using the provided connection string. The `ServerVersion.AutoDetect` feature is used to automatically detect the MySQL server version, which is necessary for EF Core to optimize its SQL queries accordingly. ### Benchmark Methods #### 1. `BulkDeleteBySaveChanges` - **Purpose:** This method tests the performance of deleting records from the database by loading entities into the application's memory, removing them from the `DbContext`, and then persisting these changes to the database using `SaveChanges()`. - **Performance Aspect:** It measures how long it takes to delete a small number of records (8 in this case) using the full ORM capabilities, including change tracking and transaction management. This approach is generally considered less efficient for bulk operations due to the overhead of loading entities and tracking changes. - **Expected Insights:** The results from this benchmark would typically show higher execution times and possibly higher memory usage, reflecting the overhead of change tracking and the round-trip to the database for each delete operation within a transaction. #### 2. `BulkDeleteByExecuteDelete` - **Purpose:** This method evaluates the performance of directly executing a delete operation in the database without loading the entities into memory, using a method like `ExecuteDelete()` (presumably an extension method or part of a library not shown in the provided code). - **Performance Aspect:** It focuses on the efficiency of bulk delete operations that bypass the DbContext's change tracking mechanism, aiming to reduce the overhead and potentially execute the operation in a single round-trip to the database. - **Expected Insights:** The expected outcome is a significantly faster execution time compared to `BulkDeleteBySaveChanges`, as this approach minimizes the overhead associated with ORM features. It's a more efficient way to handle bulk deletes when the application doesn't need to process or validate the data being removed. ### Conclusion Running these benchmarks will provide insights into the trade-offs between using EF Core's full ORM capabilities for data manipulation versus using more direct database operations for bulk actions. The `BulkDeleteBySaveChanges` method is expected to be slower and more resource-intensive, highlighting the cost of convenience and abstraction provided by EF Core. In contrast, `BulkDeleteByExecuteDelete` should demonstrate the performance benefits of bypassing the ORM layer for specific bulk operations, offering a more efficient alternative when application-level processing of the data is not required.


Benchmark Comments: