1

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?

gven21
  • 43
  • 1
  • 6
  • I'm not super familiar with Dapper, but you should probably set `DbString`'s `Length` property to 100 so that it matches your database. – DiplomacyNotWar Apr 03 '22 at 14:44
  • I did that too, same results... – gven21 Apr 03 '22 at 14:54
  • You should use "SELECT TOP 1 ...". EF has almost the same speed on simple queries. Performance difference is visible only when EF generates bad query. – Svyatoslav Danyliv Apr 03 '22 at 15:30
  • Please share the query plans for both via https://brentozar.com/pastetheplan. Did you warm the cache by running these queries at least once before benchmarking? – Charlieface Apr 03 '22 at 16:06
  • May be useful: https://stackoverflow.com/a/43590624/5779732 – Amit Joshi Apr 04 '22 at 08:27
  • @SvyatoslavDanyliv Yes, "Select Top 1" is the answer! Τhe improvement was huge... I know the performance difference is very small but the memory allocation difference is huge. That's the main reason I want to give Dapper a shot! – gven21 Apr 07 '22 at 19:21
  • @Charlieface I do not have a query plan. I am not even sure what that means... I just use the code I share! – gven21 Apr 07 '22 at 19:22
  • Give shot for [linq2db](https://github.com/linq2db/linq2db) also. Dapper is very simple lib, but `linq2db` with the same performance gives LINQ support. – Svyatoslav Danyliv Apr 07 '22 at 19:34
  • Take your query and paste it into SSMS (for the parameter you can do `DECLARE @name nvarchar(100) = 'someName'`), then click on the toolbar "Include Actual Execution Plan" and run the query. Once the plan is generated then right click it and open the XML. Copy the XML into https://brentozar.com/pastetheplan and generate a link, then [edit] and paste the link into your question. Performance questions are not answerable without execution plans – Charlieface Apr 07 '22 at 20:13

1 Answers1

2

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}'");
Charles
  • 2,329
  • 7
  • 14
  • But shouldn't the connection to the database also be tested? – gven21 Apr 07 '22 at 19:29
  • @gven21 Usually you would create the connection only once. It can stay up many hours, but might eventually need to be restarted on a disconnect – Charles Apr 09 '22 at 17:52