Dapper vs Entity Framework vs ADO.NET Performans Değerlendirilmesi

Stackoverflow tarafından geliştirilen Dapper'ın alternatiflerine karşı performans testi için internette yer alan makalede neredeyse ADO.Net hızlı olmasından dolayı ilgimi çekti ve testte bazı değişikliler yaparak tekrar test ettim.

Database üzerindeki tablolar aşağıdaki gibidir.

Daha önce yapılan testi tekrar etmek istememin sebebi Entity Framework ile çekilen datalarda Player sınıfı içerisindeki Team ve Team içerisindeki Sport sınıfının da doldurularak gelmesine karşın Ado.Net ile çekilenler collection bir sınıf üzerine eşleştirilmeden datatable olarak bırakılmış ve Dapper da ise gömülü sınıfların hiçbirisi doldurulmamıştır.

Ortalama bir değer almak için 100 istek yapılmış olup bunların toplamlarını ekrana yazdıran bir program yazıldı. Methodlar aynı sonucu döndürmekte ve her bir çalışmada

GetPlayerById metodu 1 sonuç,
GetPlayerByTeamId 1000 sonuç,
GetPlayerBySportId 18000 sonuç  döndürmüştür.

Programın çıktısı:

EntityFramework 100 x GetPlayerById : 2081
EntityFramework 100 x GetPlayersByTeamId : 3077
EntityFramework 100 x GetPlayersBySportId : 55648

AdoNet 100 x GetPlayerById : 25
AdoNet 100 x GetPlayersByTeamId : 475
AdoNet 100 x GetPlayersBySportId : 7748

DAPPER 100 x GetPlayerById : 113
DAPPER 100 x GetPlayersByTeamId : 580
DAPPER 100 x GetPlayersBySportId : 12265


Çalıştırılan Program Kaynak Kodu:

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

namespace DapperDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            long efGetPlayerById = 0;
            long efGetPlayersByTeamId = 0;
            long efGetPlayersBySportId = 0;

            long adoGetPlayerById = 0;
            long adoGetPlayersByTeamId = 0;
            long adoGetPlayersBySportId = 0;


            long dapperGetPlayerById = 0;
            long dapperGetPlayersByTeamId = 0;
            long dapperGetPlayersBySportId = 0;

            var ef = new EntityFramework();
            var ado = new AdoNet();
            var dapper = new Dapper();

            var testCount = 100;

            for (int i = 0; i < testCount; i++)
            {
                efGetPlayerById += ef.GetPlayerById(100);
                efGetPlayersByTeamId += ef.GetPlayersByTeamId(45);
                efGetPlayersBySportId += ef.GetPlayersBySportId(1);

                adoGetPlayerById += ado.GetPlayerById(100);
                adoGetPlayersByTeamId += ado.GetPlayersByTeamId(45);
                adoGetPlayersBySportId += ado.GetPlayersBySportId(1);

                dapperGetPlayerById += dapper.GetPlayerById(100);
                dapperGetPlayersByTeamId += dapper.GetPlayersByTeamId(45);
                dapperGetPlayersBySportId += dapper.GetPlayersBySportId(1);

            }


            Console.WriteLine($"EntityFramework {testCount} x GetPlayerById : {efGetPlayerById}");
            Console.WriteLine($"EntityFramework {testCount} x GetPlayersByTeamId : {efGetPlayersByTeamId}");
            Console.WriteLine($"EntityFramework {testCount} x GetPlayersBySportId : {efGetPlayersBySportId}");

            Console.WriteLine();


            Console.WriteLine($"AdoNet {testCount} x GetPlayerById : {adoGetPlayerById}");
            Console.WriteLine($"AdoNet {testCount} x GetPlayersByTeamId : {adoGetPlayersByTeamId}");
            Console.WriteLine($"AdoNet {testCount} x GetPlayersBySportId : {adoGetPlayersBySportId}");

            Console.WriteLine();


            Console.WriteLine($"DAPPER {testCount} x GetPlayerById : {dapperGetPlayerById}");
            Console.WriteLine($"DAPPER {testCount} x GetPlayersByTeamId : {dapperGetPlayersByTeamId}");
            Console.WriteLine($"DAPPER {testCount} x GetPlayersBySportId : {dapperGetPlayersBySportId}");
        }
        public static string conStr = "Server=xxx;Database=xxx;User Id=xxx;Password=xxx;";
    }


    public class EntityFramework
    {
        public long GetPlayerById(int id)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (GELISTIRMEEntities context = new GELISTIRMEEntities())
            {
                var c = context.Player.FirstOrDefault(q => q.Id == id);
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersByTeamId(int id)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (GELISTIRMEEntities context = new GELISTIRMEEntities())
            {
                var c = context.Player.Where(q => q.TeamId == id).ToList();
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersBySportId(int id)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (GELISTIRMEEntities context = new GELISTIRMEEntities())
            {
                var c = context.Player.Where(q => q.Team.SportId == id).ToList();
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
    }


    public class AdoNet
    {

        public long GetPlayerById(int id)
        {
            var sw = new Stopwatch();

            sw.Start();
            List<PlayerDto> list = new List<PlayerDto>();
            using (var con = new SqlConnection(Program.conStr))
            {
                using (var cmd = new SqlCommand("SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Name, t.FoundingDate, t.SportId, s.Name FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE p.Id = @id", con))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    con.Open();
                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        list.Add(new PlayerDto
                        {
                            Id = reader.GetInt32(0),
                            FirstName = reader.GetString(1),
                            LastName = reader.GetString(2),
                            DateOfBirth = reader.GetDateTime(3),
                            TeamId = reader.GetInt32(4),
                            Team = new TeamDto
                            {
                                Id = reader.GetInt32(4),
                                Name = reader.GetString(5),
                                FoundingDate = reader.GetDateTime(6),
                                SportId = reader.GetInt32(7),
                                Sport = new SportDto
                                {
                                    Id = reader.GetInt32(7),
                                    Name = reader.GetString(8)
                                }
                            }
                        });
                    }
                    con.Close();
                }
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersByTeamId(int id)
        {
            var sw = new Stopwatch();

            sw.Start();
            List<PlayerDto> list = new List<PlayerDto>();
            using (var con = new SqlConnection(Program.conStr))
            {
                using (var cmd = new SqlCommand("SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Name, t.FoundingDate, t.SportId, s.Name FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE t.Id = @id", con))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    con.Open();
                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        list.Add(new PlayerDto
                        {
                            Id = reader.GetInt32(0),
                            FirstName = reader.GetString(1),
                            LastName = reader.GetString(2),
                            DateOfBirth = reader.GetDateTime(3),
                            TeamId = reader.GetInt32(4),
                            Team = new TeamDto
                            {
                                Id = reader.GetInt32(4),
                                Name = reader.GetString(5),
                                FoundingDate = reader.GetDateTime(6),
                                SportId = reader.GetInt32(7),
                                Sport = new SportDto
                                {
                                    Id = reader.GetInt32(7),
                                    Name = reader.GetString(8)
                                }
                            }
                        });
                    }
                    con.Close();
                }
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersBySportId(int id)
        {
            var sw = new Stopwatch();

            sw.Start();
            List<PlayerDto> list = new List<PlayerDto>();
            using (var con = new SqlConnection(Program.conStr))
            {
                using (var cmd = new SqlCommand("SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Name, t.FoundingDate, t.SportId, s.Name FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE s.Id = @id", con))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    con.Open();
                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        list.Add(new PlayerDto
                        {
                            Id = reader.GetInt32(0),
                            FirstName = reader.GetString(1),
                            LastName = reader.GetString(2),
                            DateOfBirth = reader.GetDateTime(3),
                            TeamId = reader.GetInt32(4),
                            Team = new TeamDto
                            {
                                Id = reader.GetInt32(4),
                                Name = reader.GetString(5),
                                FoundingDate = reader.GetDateTime(6),
                                SportId = reader.GetInt32(7),
                                Sport = new SportDto
                                {
                                    Id = reader.GetInt32(7),
                                    Name = reader.GetString(8)
                                }
                            }
                        });
                    }
                    con.Close();
                }
            }
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
    }

    public class Dapper
    {
        public long GetPlayerById(int id)
        {
            var lookup = new Dictionary<int, PlayerDto>();
            var sw = new Stopwatch();
            sw.Start();
            using (var con = new SqlConnection(Program.conStr))
            {
                con.Open();
                var player = con.Query<PlayerDto, TeamDto, SportDto, PlayerDto>("SELECT p.*, t.*, s.* FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE p.Id = @id", (p, t, s) =>
                {
                    var res = new PlayerDto();
                    if (!lookup.TryGetValue(p.Id, out res))
                    {
                        lookup.Add(p.Id, res = p);
                    }
                    res.Team = t;
                    res.Team.Sport = s;
                    return res;

                }, new { id = id }).AsQueryable();

            }
            var players = lookup.Values;
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersByTeamId(int id)
        {
            var lookup = new Dictionary<int, PlayerDto>();
            var sw = new Stopwatch();
            sw.Start();
            using (var con = new SqlConnection(Program.conStr))
            {
                con.Open();
                var player = con.Query<PlayerDto, TeamDto, SportDto, PlayerDto>("SELECT p.*, t.*, s.* FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE t.Id = @id", (p, t, s) =>
                {
                    var res = new PlayerDto();
                    if (!lookup.TryGetValue(p.Id, out res))
                    {
                        lookup.Add(p.Id, res = p);
                    }
                    res.Team = t;
                    res.Team.Sport = s;
                    return res;

                }, new { id = id }).AsQueryable();

            }
            var players = lookup.Values;
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }
        public long GetPlayersBySportId(int id)
        {
            var lookup = new Dictionary<int, PlayerDto>();
            var sw = new Stopwatch();
            sw.Start();
            using (var con = new SqlConnection(Program.conStr))
            {
                con.Open();
                var player = con.Query<PlayerDto, TeamDto, SportDto, PlayerDto>("SELECT p.*, t.*, s.* FROM Player p INNER JOIN Team t ON p.TeamId = t.Id INNER JOIN Sport s ON t.SportId = s.Id WHERE s.Id = @id", (p, t, s) =>
                {
                    var res = new PlayerDto();
                    if (!lookup.TryGetValue(p.Id, out res))
                    {
                        lookup.Add(p.Id, res = p);
                    }
                    res.Team = t;
                    res.Team.Sport = s;
                    return res;

                }, new { id = id }).AsQueryable();

            }
            var players = lookup.Values;
            sw.Stop();
            return sw.ElapsedMilliseconds;
        }

    }

    public class SportDto
    {
        public int Id { get; set; }
        public string Name { get; set; }

    }
    public class TeamDto
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime FoundingDate { get; set; }
        public int SportId { get; set; }
        public virtual SportDto Sport { get; set; }
    }
    public class PlayerDto
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DateOfBirth { get; set; }
        public int TeamId { get; set; }
        public virtual TeamDto Team { get; set; }
    }

}

Orjinali: Dapper vs Entity Framework vs ADO.NET Performance Benchmarking

Dotnet Core ile Postgresql

Dotnet Core 2 SDK ve Visual Studio Code kurulu olduğu varsayılmıştır.

elephantsql.com üzerinden denemek için ücretsiz bir postgresql database alabilirsiniz.

Konsol açalım

1) Yeni bir dotnet projesi oluşturalım

mkdir projem 

cd projem

dotnet new webapi

2) Kullanacağımız paketleri kuralım

dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package Microsoft.EntityFrameworkCore.Tools

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

3)Visual Studio Code ile projeyi açalım. 

code .

4) projem.csproj içerisinde Microsoft.EntityFrameworkCore.Tools.DotNet toolunu ekleyelim.

<dotnetclitoolreference include="Microsoft.EntityFrameworkCore.Tools.DotNet" version="2.0.1">

projem.csproj son hali aşağıdaki gibidir.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.0.0" />
  </ItemGroup>

  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.1" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
  </ItemGroup>

</Project>

5) appsettings.Development.json içerisine TestConnection isminde bir bağlantı cümlesi ekleyelim

"ConnectionStrings":{

    "TestConnection":"Server=XXX;Port=5432;Database=XXX;User Id=XXX;Password=XXX;"

  },

Son hali aşağıdaki gibidir.

{
  "ConnectionStrings":{
    "TestConnection":"Server=XXX;Port=5432;Database=XXX;User Id=XXX;Password=XXX;"

  },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

6) Models adında bir klasör oluşturalım ve içerisine

User.cs sınıfını ekleyelim

namespace projem.Models
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }
}

ve TestContext.cs ekleyelim

using Microsoft.EntityFrameworkCore;

namespace projem.Models
{
    public class TestContext : DbContext
    {
        public TestContext(DbContextOptions<TestContext> options) : base(options) { }
        public DbSet<User> Users { get; set; }
    }
}

7. Sonuncu ve en önemli adım Startup.cs içerisinde ConfigureService metodunun içerisine EntityFrameworkNpgsql() servisini ekleyelim

 
 services.AddEntityFrameworkNpgsql().AddDbContext<testcontext>(opt =>
                opt.UseNpgsql(Configuration.GetConnectionString("TestConnection"))
            );

Startup.cs en son hali aşağıdaki gibidir.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using projem.Models;


namespace projem
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
             services.AddEntityFrameworkNpgsql().AddDbContext<testcontext>(opt =>
                opt.UseNpgsql(Configuration.GetConnectionString("TestConnection"))
            );
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }
}

8. Projeyi Kaydet Komut satırına

dotnet ef migrations add InitialCreate

ile ilk migration oluşturdurk ve

dotnet ef database update

ile tarafımızda olan değişiklikeri database üzerinde yansıttık. https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet