Is Dapper's QueryFirstOrDefault method really slow?

Question

I read that Dapper is faster than EF (at least at retrieving data) and I want to confirm that so I am comparing Dapper and EntityFramework with the help of BenchmarkDotNet.

So I tried this...

    [Benchmark]
    public Player EntityFramework_GetByName()
    {
        using (ApplicationDbContext context = new())
        {
            return context.Players.FirstOrDefault(x => x.FirstName == _name);
        }
    }

    [Benchmark]
    public Player Dapper_GetByName()
    {
        using (SqlConnection conn = new(Database.ConnectionString))
        {
            return conn.QueryFirstOrDefault<Player>($"SELECT * FROM Players WHERE FirstName = '{_name}'");
        }
    }

But the result are not what I expecting...

Then I read here about the column type "problem" and how that can affect the performance, so I change the type of the column to NVarchar with max length of 100 and my code for the Dapper to this

    [Benchmark]
    public Player Dapper_GetByName()
    {
        using (SqlConnection conn = new(Database.ConnectionString))
        {
            return conn.QueryFirstOrDefault<Player>($"SELECT * FROM Players WHERE FirstName = @name", new 
            { @name = new DbString { Value = _name, IsAnsi = false } });
        }
    }

The results of the benchmark tests are the following..

Method	Mean	Error	StdDev	Allocated
Dapper_GetByName	41,092.8 us	1,400.39 us	4,085.0 us	4 KB
EntityFramework_GetByName	2,971.6 us	305.43 us	895.8 us	110 KB

The difference is very big. Is there a way to improve this?

Answer 1

Uhm, maybe you should not compare

// Open and Close a completely new database connection
using (SqlConnection conn = new(Database.ConnectionString))

vs

// Create a new Unit of Work / Transaction
using (ApplicationDbContext context = new())

Benchmark only the inner part:

return conn.QueryFirstOrDefault<Player>($"SELECT * FROM Players WHERE FirstName = '{_name}'");

Answer 2

I think this example shows very clearly the responsibility of SQL query generation when using Dapper, CA.Blocks.DataAccess or ADO.NET directly. When using these packages for accessing the database the developer is entirely in charge of the SQL query, its projection and execution. When using EF the responsibility of generating the query is removed from the developer and delegated to EF. This is a double-edged sword and can result in good queries as well as very bad queries. Most of the performance gains made in Dapper are from having full control over the SQL and eliminating bad SQL generation. The converse is also true, most of the performance problems with Dapper when compared to EF are due to EF creating a better query. So what is happening here. In simple terms EF has looked at the request and has knowledge that you only what the first record FirstOrDefault so its query generation has resulted in

SELECT TOP 1 * FROM … WHERE…

The Dapper query you are making the comparison with is

SELECT * FROM … WHERE …

So the difference I suspect is purely on SQL. The Test database used, probably has many records in the Person table. Given the numbers it is likely that there is no index on name resulting in a Table Scan to find the matching data.

In the query generated by EF the database can stop the execute as soon as it finds the first record, in the Dapper example the database assembles the full record set with all the matches based on name then sends that row-set. Dapper is simply reading the first row and closing the connection.

To make this a fair comparison you need to change the query to be top 1. Like

 [Benchmark]
    public Player Dapper_GetByName()
    {
        using (SqlConnection conn = new(Database.ConnectionString))
        {
            return conn.QueryFirstOrDefault<Player>($"SELECT Top 1 * FROM Players WHERE FirstName = @name", new 
            { @name = new DbString { Value = _name, IsAnsi = false } });
        }
    }

Also, the decision to go with Dapper for performance means you need to get to know and love SQL.