ADO.NET vs Dapper Performance Benchmark

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

Category Performance | Tags: ADO.NET, Dapper, Benchmark, Database

Published: 05 June 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. As always, we also have the option of using straight ADO.NET queries.

Thing is, because performance needs to be front-and-center in this app, I'd like to be really sure which of these ORMs provide the best bang for my buck.

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 we needed was a set of queries that we 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 ADO.NET and Dapper.NET test classes:

public class ADONET : 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";
            using(SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
            {
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@ID", id));
                DataTable table = new DataTable();
                adapter.Fill(table);
            }
        }
        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";
            using(SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
            {
                adapter.SelectCommand.Parameters
                    .Add(new SqlParameter("@ID", teamId));
                DataTable table = new DataTable();
                adapter.Fill(table);
            }
        }
        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 Player p INNER JOIN Team t 
            ON p.TeamId = t.Id WHERE t.SportId = @ID";
            using(SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
            {
                adapter.SelectCommand.Parameters
                    .Add(new SqlParameter("@ID", sportId));
                DataTable table = new DataTable();
                adapter.Fill(table);
            }
        }
        watch.Stop();
        return watch.ElapsedMilliseconds;
    }
}

 

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, newID = 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;
    }
}


Note that in Dapper.NET and ADO.NET's cases, we will be selecting a row for each Player in the GetTeamsForSport query. This is not an exact comparison, 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.


ADO.NET Results

RunPlayer by IDPlayers for TeamTeams for Sport
10.01ms1.03ms10.25ms
20ms1ms11ms
30.1ms1.03ms9.5ms
40ms1ms9.62ms
50ms1.07ms7.62ms
60.02ms1ms7.75ms
70ms1ms7.62ms
80ms1ms8.12ms
90ms1ms8ms
100ms1.17ms8.88ms
Average0.013ms1.03ms8.84ms


Dapper.NET Results

RunPlayer by IDPlayers for TeamTeams for Sport
10.38ms1.03ms9.12ms
20.03ms1ms8ms
30.02ms1ms7.88ms
40ms1ms8.12ms
50ms1.07ms7.62ms
60.02ms1ms7.75ms
70ms1ms7.62ms
80ms1.02ms7.62ms
90ms1ms7.88ms
100.02ms1ms7.75ms
Average0.047ms1.01ms7.94ms



Analysis

As we can see in the data above ADO.NET and Dapper.NET have the same performance.

It is interesting to me is that Dapper.NET was, on average, faster than ADO.NET for the more complex queries. It appears to me that there is a performance hit the first time you use Dapper.NET (as also appears to happen with EF) but once you get past that, Dapper.NET is amazingly fast  I suspect that this has something to do with the fact that in the ADO.NET test cases we are using a SqlDataAdapter, though I cannot prove this.

The data shows that, at least in terms of raw speed and with these queries, Dapper.NET will (narrowly) be the fastest. Which is why my ultimate conclusion might surprise you.

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. A plan to develop a project is 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. 

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, and therefore development time will be quicker.

In short, Dapper.NET is slightly faster than straight ADO.NET. I think this will strike a balance between ease of development and performance.