Benchmark.NET is a “Powerful .NET Library for Benchmarking” (link to Github, here). Simply, decorate a function with the Benchmark attribute and run the system in release mode and it’ll calculate mean, error, standard deviation, median, and even memory used. So I thought I’d use it to do a few comparisons between Entity Framework Core 6 and ADO.NET. The code for this project can be found here.
Open the solution in Visual Studio and you’ll notice two projects:
- database – this contains all database calls as well as migrations
- efcorevsado – this is a console application which will seed the database or run the benchmarks
Setup and Execution
After cloning the repo, and opening the solution in Visual Studio, you will need to run the migration to generate the database:
dotnet ef database update --project databaseNext, you will need to seed the database with test data. To do this, run the efcorevsado project with the “–seed” command line argument. This will generate 5000 ARows each containing 25 BRows (more on that later).
Now, you can run the benchmarks via running the efcorevsado project with the “–run” command line argument. This will take a few minutes and it will generate the results when complete. Let’s look at the database configuration.
Database Configuration
Lets look at the database project:
public class A
{
public long Id { get; set; }
public string Name { get; set; }
public ICollection<B> Bs { get; set; } = new List<B>();
} public class B
{
public long Id { get; set; }
public string Name { get; set; }
public long AId { get; set; }
[ForeignKey("AId")]
public A A { get; set; }
}
There are 2 tables: ARows, and BRows. ARows contain the A object and BRows contain the B object.
- The A object contains an autogenerated Id (identity) as well as a string name, and a list of B objects (foreign key).
- The B object contains an autogenerated Id (identity) and a name and also a foreign key reference to the A object.
The database calls are being handled by the “DataAccess.cs” class:
public class DataAccess
{
private readonly EfDbContext dbContext = new EfDbContext();
public DataAccess()
{
}
public async Task<List<A>> GetFromEf(List<long> lst)
{
return await dbContext.ARows.AsNoTracking()
.Include(x => x.Bs)
.Where(x => lst.Contains(x.Id))
.OrderBy(x => x.Id)
.ToListAsync();
}
public async Task<List<A>> GetFromAdo(List<long> lst)
{
var con = new SqlConnection(EfDbContext.ConnectionString);
var cmd = new SqlCommand(@$"SELECT A.Id, A.Name, B.Id as BId, B.Name as BName
FROM AROWS A JOIN BROWS B ON B.AID=A.ID
WHERE A.Id in ({string.Join(',', lst)})
ORDER BY A.Id ASC", con);
var retLst = new List<A>();
try
{
con.Open();
using(var rdr = cmd.ExecuteReader())
{
var a = new A();
while (await rdr.ReadAsync())
{
var aId = rdr.GetInt64(0);
if(a.Id != aId)
{
a = new A();
a.Id = aId;
a.Name = rdr.GetString(1);
retLst.Add(a);
}
var b = new B()
{
Id = rdr.GetInt64(2),
Name = rdr.GetString(3),
AId = aId,
};
a.Bs.Add(b);
}
}
}
finally
{
con?.Close();
}
return retLst;
}
public async Task<long> InsertEf(string name)
{
var a = new A()
{
Name = name
};
dbContext.ARows.Add(a);
await dbContext.SaveChangesAsync();
return a.Id;
}
public async Task<long> InsertAdo(string name)
{
var con = new SqlConnection(EfDbContext.ConnectionString);
var cmd = new SqlCommand(@$"INSERT INTO AROWS OUTPUT INSERTED.ID VALUES(@name)", con);
cmd.Parameters.AddWithValue("name", name);
try
{
con.Open();
return (long)await cmd.ExecuteScalarAsync();
}
finally
{
con.Close();
}
}
}
- GetFromEf – retrieves all A Rows (and associated B Rows) in which the provided list contains the Id of A using Entity Framework.
- GetFromAdo – does the same thing, except it uses ADO.NET. Notice how much longer this function is over “GetFromEf”. Performance at a price.
- InsertEf – Using Entity Framework, this inserts a single A row into the database, returning the Id that it was give.
- InsertAdo – Does the same thing as the previous, but uses ADO.NET.
Benchmark Configuration
The Benchmark.cs file (in the efcorevsado project) contains the benchmark configuration:
[MemoryDiagnoser]
public class Benchmarks
{
[Benchmark]
[ArgumentsSource(nameof(SearchData))]
public async Task EntityFrameworkSearch(List<long> lst)
{
var da = new DataAccess();
await da.GetFromEf(lst);
}
[Benchmark]
[ArgumentsSource(nameof(SearchData))]
public async Task AdoSearch(List<long> lst)
{
var da = new DataAccess();
await da.GetFromAdo(lst);
}
public IEnumerable<object> SearchData()
{
yield return Enumerable.Range(0, 1000).Select(x => Convert.ToInt64(x)).ToList();
yield return Enumerable.Range(1000, 1000).Select(x => Convert.ToInt64(x)).ToList();
yield return Enumerable.Range(2000, 1000).Select(x => Convert.ToInt64(x)).ToList();
yield return Enumerable.Range(3000, 1000).Select(x => Convert.ToInt64(x)).ToList();
yield return Enumerable.Range(4000, 1000).Select(x => Convert.ToInt64(x)).ToList();
}
[Benchmark]
[ArgumentsSource(nameof(InsertData))]
public async Task EntityFrameworkInsert(string name)
{
var da = new DataAccess();
await da.InsertEf(name);
}
[Benchmark]
[ArgumentsSource(nameof(InsertData))]
public async Task AdoFrameworkInsert(string name)
{
var da = new DataAccess();
await da.InsertAdo(name);
}
public IEnumerable<object> InsertData()
{
yield return "test1";
yield return "test2";
yield return "test3";
yield return "test4";
}
}
Notice, firstly, there a “MemoryDiagnoser” attribute at the top of the class. This tells Benchmark.NET to track memory usage.
- EntityFrameworkSearch – This function retrieves all data in which the A’s Id is contained in a list of longs (the id type) using Entity Framework. The “Benchmark” attribute tells Benchmark.NET to use this for testing. The “ArgumentsSource” attribute tells Benchmark.NET to use result of the function “SearchData” as test data for the list. Notice, searchdata returns 1k id’s for each test.
- AdoSearch – This performs the same action as the above, but uses ADO.NET.
- EntityFrameworkInsert – Inserts a single row in the ARows table using Entity Framework. Notice, it uses the InsertData function for source data.
- AdoFrameworkInsert – This performs the same action as the previous, but uses ADO.NET.
Looking at the Results
Ok, now that everything is setup, let’s look at the results of Benchmark.NET:
The results are a bit surprising. On average, Entity Framework takes twice as long to retrieve the rows, and it uses more the twice the memory (26 Megs vs 10 Megs). I keep reading that EF Core is so fast and efficient.
When inserting a record, Entity Framework is about 5 times slower and uses roughly 10x the memory. Now, this needs to be taken with a grain of salt, because it’s only inserting one record. I’d like to compare the frameworks with more than one insert at a time (in a later blog post). Maybe, 10 and 100 inserts. I’m pretty sure the results would be closer.
That’s all I have for today. Thanks for stopping by!