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
