1
Entity Framework Bulk Update, normal update, plain SQL and ADO.NET plain SQL benchmark on EF 8
Date Added (UTC):
11 Apr 2024 @ 01:45
Date Updated (UTC):12 Apr 2024 @ 01:50
.NET Version(s): Tag(s):
#DBOperations #EntityFramework
Added By:
.NET Developer and tech lead from Ireland!
Benchmark Results:
Benchmark Code:
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Columns;
using BenchmarkDotNet.Configs;
using BenchmarkDotNet.Jobs;
using BenchmarkDotNet.Reports;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
using System;
using System.Linq;
namespace EntityFrameworkBenchmarks
{
[Config(typeof(Config))]
[SimpleJob(RuntimeMoniker.Net80)]
[MemoryDiagnoser]
[HideColumns(Column.AllocRatio, Column.RatioSD, Column.Median, Column.Gen0, Column.Gen1)]
public class EFBulkUpdateBenchmarks
{
public string SQLConnectionString
= "Server=localhost;Database=Blogging;Trusted_Connection=True;TrustServerCertificate=true;";
[Params(10, 100)]
public int records;
[GlobalSetup]
public void GlobalSetup()
{
using var context = new MyContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var random = new Random();
var date = DateTime.UtcNow;
for (int i = 0; i < records; i++)
{
context.Add(new
Blog
{
Url = "https://blogurl/ID/" + random.Next(1, 10000).ToString(),
LastUpdated = date
});
}
context.SaveChanges();
}
[Benchmark]
public void ADONET_PlainSQL_BulkUpdate()
{
using (SqlConnection connection = new SqlConnection(SQLConnectionString))
{
connection.Open();
using (SqlCommand command =
new SqlCommand("UPDATE [Blogs] SET [LastUpdated] = @date; --ADONETUpdate", connection))
{
command.Parameters.AddWithValue("@date", DateTime.UtcNow);
command.ExecuteNonQuery();
}
}
}
[Benchmark]
public void EF_PlainSQL_BulkUpdate()
{
using var context = new MyContext();
context.Database
.ExecuteSql($"UPDATE [Blogs] SET [LastUpdated] = {DateTime.UtcNow}; --PlainSQLUpdate");
}
[Benchmark(Baseline = true)]
public void EF_ExecuteUpdate_BulkUpdate()
{
using var context = new MyContext();
context.Blogs.ExecuteUpdate(
s => s.SetProperty(
blog => blog.LastUpdated, DateTime.UtcNow));
}
[Benchmark]
public void EF_SaveChanges_NoBulkUpdate()
{
using var context = new MyContext();
var date = DateTime.UtcNow;
var blogs = context.Blogs.ToList();
foreach (var blog in blogs)
{
blog.LastUpdated = date;
}
context.SaveChanges();
}
private class Config : ManualConfig
{
public Config()
{
SummaryStyle =
SummaryStyle.Default.WithRatioStyle(RatioStyle.Percentage);
}
}
public class MyContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer(@"Server=localhost;Database=Blogging;Trusted_Connection=True;TrustServerCertificate=true");
}
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public bool IsActive { get; set; }
public DateTime LastUpdated { get; set; }
}
}
}
Powered by SharpLab
// .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 'Data' does not exist in the namespace 'Microsoft' (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 'DbSet<>' could not 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 CS1674: 'EFBulkUpdateBenchmarks.MyContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'Add' and no accessible extension method 'Add' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)
Powered by SharpLab
// .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 'Data' does not exist in the namespace 'Microsoft' (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 'DbSet<>' could not 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 CS1674: 'EFBulkUpdateBenchmarks.MyContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'Add' and no accessible extension method 'Add' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'EFBulkUpdateBenchmarks.MyContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'EFBulkUpdateBenchmarks.MyContext' could be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)
Powered by SharpLab
|
Benchmark Description:
This benchmark was taken on Entity Framework 8 and Microsoft.Data.SqlClient (5.0.0.0) when the DB was local to the benchmark code.
We can see that as the number of records to update goes up the difference between the regular update (where EF needs to get all blogs into context first) and the ExecuteUpdate method (new in EF7) gets bigger. If the DB was not on localhost relative to the benchmark execution code the difference would likely be larger.
The difference between the three single command update is marginal.
This benchmark setup is designed to evaluate and compare different methods of updating a large number of records in a database using Entity Framework (EF) Core and ADO.NET with a SQL Server database. The benchmarks are configured to run on .NET 8.0, as indicated by the `[SimpleJob(RuntimeMoniker.Net80)]` attribute. Memory usage is being diagnosed with `[MemoryDiagnoser]`, and certain columns are hidden in the output to focus on the most relevant metrics through `[HideColumns(Column.AllocRatio, Column.RatioSD, Column.Median, Column.Gen0, Column.Gen1)]`. The benchmarks vary the number of records updated in each run, as specified by the `[Params(10, 100)]` attribute, to observe how the performance scales with the number of records.
### ADONET_PlainSQL_BulkUpdate
**Purpose**: This method measures the performance of using raw ADO.NET to execute a bulk update SQL command directly. It's designed to serve as a baseline for understanding how much overhead, if any, EF Core adds to the operation.
**Rationale**: Direct SQL operations are generally expected to be faster than ORM-based approaches because they involve less overhead. This benchmark is important as it provides a performance reference point for the other methods that use EF Core.
**Expected Insights**: Lower execution times and resource usage compared to EF Core methods, demonstrating the efficiency of using direct SQL commands for bulk operations.
### EF_PlainSQL_BulkUpdate
**Purpose**: This benchmark tests the performance of executing a raw SQL update command through EF Core's `ExecuteSql` method. It aims to assess whether using EF Core to execute direct SQL commands offers any performance benefits or drawbacks compared to pure ADO.NET.
**Rationale**: While still using raw SQL, this method evaluates the overhead of initiating the command through EF Core. It's crucial for understanding the trade-offs between using EF for convenience versus the potential performance impact.
**Expected Insights**: Slightly higher execution times and resource usage than pure ADO.NET, due to the additional layers of abstraction in EF Core. However, it might offer a good balance between performance and the benefits of EF Core's integrated environment.
### EF_ExecuteUpdate_BulkUpdate
**Purpose**: This method evaluates the performance of EF Core's `ExecuteUpdate` method, which is designed for bulk updates in a strongly-typed manner. It serves as the baseline for EF Core methods in this benchmark.
**Rationale**: It tests EF Core's ability to handle bulk updates efficiently using its built-in methods, which are expected to be more developer-friendly than writing raw SQL. This benchmark is key to understanding how well EF Core performs in scenarios requiring bulk data manipulation.
**Expected Insights**: This method might show higher execution times compared to raw SQL approaches due to the added overhead of EF Core's change tracking and query generation. However, it benefits from being strongly-typed and integrated within the EF Core API, reducing the likelihood of SQL errors and improving developer productivity.
### EF_SaveChanges_NoBulkUpdate
**Purpose**: This benchmark measures the performance of updating records individually within EF Core's context and then saving all changes at once using `SaveChanges`. It's designed to highlight the performance impact of EF Core's change tracking and unit-of-work pattern when not using bulk operations.
**Rationale**: This method represents a common but suboptimal approach to updating multiple records with EF Core. It's important for illustrating the potential performance pitfalls of not using bulk operations for large updates.
**Expected Insights**: Significantly higher execution times and resource usage compared to the other methods, due to the overhead of tracking each record's state individually and generating separate SQL update commands for each record. This benchmark underscores the importance of using bulk operations for large-scale updates.
Overall, these benchmarks are designed to provide a comprehensive view of the performance characteristics of different update strategies in EF Core and ADO.NET, helping developers make informed decisions based on their specific requirements and constraints.