Entity Framework vs Dapper Performance Benchmark

Because performance needs to be front-and-center in this app, I'd like to be really sure which ORM provides the best bang for my buck.

Category Performance | Tags: Entity Framework, Dapper, Benchmark, Database

Published: 02 July 2020

We generally want to use Entity Framework for our ORM, but just a little searching reveals StackExchange questions and blog post after blog post detailing how EF is simply not up to par for high-performance systems. Into that gap steps so-called "micro-ORMs" like Dapper.NET (which is used on the StackExchange family of sites including StackOverflow) which promise performance at the cost of maintainability.

Methodology

This test uses a database schema that includes a Sport that has many Teams, and a Team that has many Players. We need some sample data to test against.

Now what I needed was a set of queries that I could create in each ORM and test against. I chose three different queries:

Player by ID
Players per Team
Teams per Sport (including Players)

For each Query, I will run the test against all data in the database (e.g. for Player by ID I will select each player by their ID) and average the total time it takes to execute the query (including setting up the DbContext or SqlConnection, as the case may be) for each execution. Then, I will do multiple runs of this over the same data so that I can average them out and get a set of numbers that should clearly show which of the ORMs is the fastest.

Test Setup

As an example, here's the code for the Entity Framework and Dapper.NET test classes:

public class Dapper : ITestSignature
{
    public long GetPlayerByID(int id)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SqlConnection conn  = new SqlConnection(Constants.ConnectionString))
        {
            conn.Open();
            const string query = @"SELECT Id, FirstName, LastName, DateOfBirth, 
            TeamId FROM Player WHERE Id = @ID"
            var player = conn.Query<PlayerDTO>(query, new{ ID = id});
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }

    public long GetPlayersForTeam(int teamId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SqlConnection conn  = new SqlConnection(Constants.ConnectionString))
        {
            conn.Open();
            const string query = @"SELECT Id, FirstName, LastName, DateOfBirth, 
            TeamId FROM Player WHERE TeamId = @ID";
            var players = conn.Query<List<PlayerDTO>>(query, new { ID = teamId });
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }

    public long GetTeamsForSport(int sportId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SqlConnection conn  = new SqlConnection(Constants.ConnectionString))
        {
            conn.Open();
            const string query = @"SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, 
            p.TeamId, t.Id as TeamId, t.Name, t.SportId FROM Team t INNER JOIN Player p 
            ON t.Id = p.TeamId WHERE t.SportId = @ID";
            var players = conn.Query<PlayerDTO, TeamDTO, PlayerDTO>(query, (player, team) => 
            {
                 return player; 
            }, splitOn: "TeamId", param: new { ID = sportId });
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }
}

public class EntityFramework : ITestSignature
{
    public long GetPlayerByID(int id)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContext context = new SportContext())
        {
            var player = context.Players
                .Where(x => x.Id == id).First();
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }

    public long GetPlayersForTeam(int teamId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContext context = new SportContext())
        {
            var players = context.Players
                .Where(x => x.TeamId == teamId).ToList();
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }

    public long GetTeamsForSport(int sportId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContext context = new SportContext())
        {
            var players = context.Teams
                .Include(x=>x.Players)
                .Where(x => x.SportId == sportId).ToList();
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }
}

Note that in Dapper.NET case, we will be selecting a row for each Player in the GetTeamsForSport query. This is not an exact comparison against the EF query, but for my purposes it works fine.

Results

The following results are for 10 iterations, each containing 8 sports, 30 teams in each sport, and 100 players per team.

Dapper.NET Results

Run	Player by ID	Players for Team	Teams for Sport
1	0.38ms	1.03ms	9.12ms
2	0.03ms	1ms	8ms
3	0.02ms	1ms	7.88ms
4	0ms	1ms	8.12ms
5	0ms	1.07ms	7.62ms
6	0.02ms	1ms	7.75ms
7	0ms	1ms	7.62ms
8	0ms	1.02ms	7.62ms
9	0ms	1ms	7.88ms
10	0.02ms	1ms	7.75ms
Average	0.047ms	1.01ms	7.94ms

Entity Framework Results

Run	Player by ID	Players for Team	Teams for Sport
1	1.64ms	4.57ms	127.75ms
2	0.56ms	3.47ms	112.5ms
3	0.17ms	3.27ms	119.12ms
4	1.01ms	3.27ms	106.75ms
5	1.15ms	3.47ms	107.25ms
6	1.14ms	3.27ms	117.25ms
7	0.67ms	3.27ms	107.25ms
8	0.55ms	3.27ms	110.62ms
9	0.37ms	4.4ms	109.62ms
10	0.44ms	3.43ms	116.25ms
Average	0.77ms	3.57ms	113.45ms

Analysis

As we can see in the data above Entity Framework is markedly slower than Dapper.NET, on the order of 3-10 times slower.

Let's be clear: the methodology used in this test had something to do with this, particularly the "Teams per Sport" query. In that query, Entity Framework was selecting both the teams in a given sport and the players involved with each team (via an Include() statement), whereas the Dapper.NET queries were just selecting joined data. In a more rigorous statistical study, the test would either be improved or these results would be thrown out.

Even if you do throw out the "Teams per Sport" query, you're still left with EF being at least 3 times slower than Dapper.NET. The data shows that, at least in terms of raw speed and with these queries, Entity Framework will be the slowest option, and Dapper.NET will be the fastest.

Conclusion

We're going to use Dapper.NET on our project; that much is not in doubt. However, we're not going to start development with it, and it will not be the only ORM in use. The plan is to develop this project using Entity Framework, and later optimize to use Dapper.NET in certain scenarios where the system needs a performance boost. Yes, we are going with the slowest option to start. Why would we do this?

Because the major drawback to using Dapper.NET is that you have naked SQL queries in your code. If anybody fat-fingers anything, we won't be aware of any issues until we run the tests against the code. Plus, the members of my group are more familiar with EF than Dapper.NET, and therefore development time will be quicker.

In short, Dapper.NET is unquestionably faster than EF, but we'll be doing the majority of development in EF and then optimizing with Dapper.NET where needed. We think this will strike a balance between ease of development and performance.