Entity Framwork Vs ADO .Net Vs Dapper .Net 效率比较

由我另一个博客拷贝
近年来一直使用EF+.Net MVC来编写系统，由于最近换了一个公司，碰到一个印度同事，他告诉我用Dapper比用EF高效很多，基本可以接近ADO .Net 的效率，因为习惯的问题一直没有对ORM做深入的了解的解析，所以借此机会对此三种方法进行效率测试，以便今后做大型数据处理时可以有更多的选择性。测试用例借鉴了 here。

比较版本

EF 6.2.0， Dapper 1.50.4

数据表结构

    public class Sport    {        public Sport()        {            Teams = new HashSet<Team>();        }        [Key]        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        public int Id { get; set; }        public string Name { get; set; }        public ICollection<Team> Teams { get; set; }    }

    public class Team    {        public Team()        {            Players = new HashSet<Player>();        }        [Key]        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        public int Id { get; set; }        public string Name { get; set; }        public DateTime FoundingDate { get; set; }        [ForeignKey("Sport")]        public int SportId { get; set; }        public Sport Sport { get; set; }        public ICollection<Player> Players { get; set; }    }

    public class Player    {        [Key]        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        public int Id { get; set; }        public string  FirstName { get; set; }        public string  LastName { get; set; }        public DateTime DateOfBirth { get; set; }        [ForeignKey("Team")]        public int TeamId { get; set; }        public Team Team { get; set; }    }

数据

Sports表加入47个不同的运动项目，大家可以网上自行搜索后加入，
Teams随即生产1000个Team，并随机分配到Sport
Players随机生成100万个Player，并随机分配到Team
数据库由EF Code First生成，并且使用比较熟悉的EF加入数据

查询Interface.

    public interface ITestORM    {        long GetPlayerByID(int id);        long GetPlayersForTeam(int teamId);        long GetPlayersForSport(int sportId);    }

GetPlayerByID: 根据运动员编号（主键）查找运动员
GetPlayersForTeam: 查找给定Team编号的所有运动员
GetPlayersForSport: 查找给定Sport编号的所有运动员
所以方法都不返回查找的结果，只是返回一个tick值（一千万分之一秒）
每个方法运行10次，并且取得平均值和搜寻的总时长进行比较

Entity Framework 实现代码

    public class EFORM : ITestORM    {        private bool _tracking = true;        public EFORM()        {        }        public EFORM(bool tracking)        {            _tracking = tracking;        }        public long GetPlayerByID(int id)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (TestORMContext context = new TestORMContext())            {                if (!_tracking)                {                    var player = context.Players.AsNoTracking().Where(x => x.Id == id).First();                }                else                {                    var player = context.Players.Where(x => x.Id == id).First();                }            }            watch.Stop();            return watch.ElapsedMilliseconds;        }        public long GetPlayersForTeam(int teamId)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (TestORMContext context = new TestORMContext())            {                if (!_tracking)                {                    var players = context.Players.AsNoTracking().Where(x => x.TeamId == teamId).ToList();                }                else                {                    var players = context.Players.Where(x => x.TeamId == teamId).ToList();                }            }            watch.Stop();            return watch.ElapsedMilliseconds;        }        public long GetPlayersForSport(int sportId)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (TestORMContext context = new TestORMContext())            {                if (!_tracking)                {                    var players = context.Players.AsNoTracking().Where(player => player.Team.Sport.Id == sportId).ToList();                }                else                {                    var players = context.Players.Where(player => player.Team.Sport.Id == sportId).ToList();                }            }            watch.Stop();            return watch.ElapsedMilliseconds;        }    }

创建EF查询对象时可以用 new EFROM（）–可tracking和new EFROM（false）–不可tracking进行查询，以便区分打开和关闭tracking时所需的查询时间，因为在上述网站上有网友提出不加tracking时，EF效率也很高效

ADO .Net 实现代码

    public class ADO:ITestORM    {        public long GetPlayerByID(int id)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT Id, FirstName, LastName, DateOfBirth, TeamId FROM Players WHERE Id = @ID", 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(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT Id, FirstName, LastName, DateOfBirth, TeamId FROM Players WHERE TeamId = @ID", conn))                {                    adapter.SelectCommand.Parameters.Add(new SqlParameter("@ID", teamId));                    DataTable table = new DataTable();                    adapter.Fill(table);                }            }            watch.Stop();            return watch.ElapsedMilliseconds;        }        public long GetPlayersForSport(int sportId)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Id as TeamId, t.Name, t.SportId FROM Players p INNER JOIN Teams t ON p.TeamId = t.Id WHERE t.SportId = @ID", conn))                {                    adapter.SelectCommand.Parameters.Add(new SqlParameter("@ID", sportId));                    DataTable table = new DataTable();                    adapter.Fill(table);                }            }            watch.Stop();            return watch.ElapsedMilliseconds;        }    }

Dapper .Net 实现代码

    public class DapperORM:ITestORM    {        public long GetPlayerByID(int id)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                var player = conn.Query<Player>("SELECT Id, FirstName, LastName, DateOfBirth, TeamId FROM Players WHERE Id = @ID", new { ID = id });            }            watch.Stop();            return watch.ElapsedMilliseconds;        }        public long GetPlayersForTeam(int teamId)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                var players = conn.Query<List<Player>>("SELECT Id, FirstName, LastName, DateOfBirth, TeamId FROM Players WHERE TeamId = @ID", new { ID = teamId });            }            watch.Stop();            return watch.ElapsedMilliseconds;        }        public long GetPlayersForSport(int sportId)        {            Stopwatch watch = new Stopwatch();            watch.Start();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestORM"].ToString()))            {                conn.Open();                var players = conn.Query<Player, Team, Player>("SELECT p.Id, p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Id as TeamId, t.Name, t.SportId FROM Teams t "                    + "INNER JOIN Players p ON t.Id = p.TeamId WHERE t.SportId = @ID", (player, team) => { return player; }, splitOn: "TeamId", param: new { ID = sportId });            }            watch.Stop();            return watch.ElapsedMilliseconds;        }    }

代码说明

上述代码基本都借鉴于 此处，不是完全拷贝，两者有部分地方有区别

测试说明

每种方法中的每个查询函数分别运行10次，每次查询的id为总记录的1/10，分别记录每个查询的时长，EF查询两次，一次tracking，一次no tracking

测试结果

说明: 运动员的编号没有从1开始，所以有下面奇怪的运动员编号 ：P

EF 有 Tracking

查询 1 Tick 查询 2 Tick 查询 3 Tick GetPlayerByID(1006820) 1801 GetPlayersForTeam(1) 13 GetPlayersForSport(1) 501 GetPlayerByID(1106820) 1 GetPlayersForTeam(101) 11 GetPlayersForSport(5) 312 GetPlayerByID(1206820) 1 GetPlayersForTeam(201) 11 GetPlayersForSport(9) 284 GetPlayerByID(1306820) 1 GetPlayersForTeam(301) 20 GetPlayersForSport(13) 363 GetPlayerByID(1406820) 1 GetPlayersForTeam(401) 24 GetPlayersForSport(17) 457 GetPlayerByID(1506820) 1 GetPlayersForTeam(501) 11 GetPlayersForSport(21) 281 GetPlayerByID(1606820) 0 GetPlayersForTeam(601) 14 GetPlayersForSport(25) 417 GetPlayerByID(1706820) 1 GetPlayersForTeam(701) 17 GetPlayersForSport(29) 344 GetPlayerByID(1806820) 1 GetPlayersForTeam(801) 38 GetPlayersForSport(33) 266 GetPlayerByID(1906820) 1 GetPlayersForTeam(901) 11 GetPlayersForSport(37) 327 平均 180.9 17 355.2 总时长 5531

EF 无 Tracking

查询 1 Tick 查询 2 Tick 查询 3 Tick GetPlayerByID(1006820) 1760 GetPlayersForTeam(1) 4 GetTeamsForSport(1) 247 GetPlayerByID(1106820) 1 GetPlayersForTeam(101) 3 GetTeamsForSport(5) 180 GetPlayerByID(1206820) 1 GetPlayersForTeam(201) 3 GetTeamsForSport(9) 171 GetPlayerByID(1306820) 1 GetPlayersForTeam(301) 3 GetTeamsForSport(13) 172 GetPlayerByID(1406820) 1 GetPlayersForTeam(401) 4 GetTeamsForSport(17) 197 GetPlayerByID(1506820) 1 GetPlayersForTeam(501) 3 GetTeamsForSport(21) 171 GetPlayerByID(1606820) 1 GetPlayersForTeam(601) 3 GetTeamsForSport(25) 179 GetPlayerByID(1706820) 1 GetPlayersForTeam(701) 3 GetTeamsForSport(29) 181 GetPlayerByID(1806820) 1 GetPlayersForTeam(801) 3 GetTeamsForSport(33) 172 GetPlayerByID(1906820) 1 GetPlayersForTeam(901) 3 GetTeamsForSport(37) 175 平均 177 3 185 总时长 3646

ADO .Net

查询 1 Tick 查询 2 Tick 查询 3 Tick GetPlayerByID(1006820) 79 GetPlayersForTeam(1) 4 GetPlayersForSport(1) 1834 GetPlayerByID(1106820) 0 GetPlayersForTeam(101) 3 GetPlayersForSport(5) 184 GetPlayerByID(1206820) 0 GetPlayersForTeam(201) 3 GetPlayersForSport(9) 180 GetPlayerByID(1306820) 0 GetPlayersForTeam(301) 3 GetPlayersForSport(13) 184 GetPlayerByID(1406820) 0 GetPlayersForTeam(401) 3 GetPlayersForSport(17) 207 GetPlayerByID(1506820) 0 GetPlayersForTeam(501) 3 GetPlayersForSport(21) 192 GetPlayerByID(1606820) 0 GetPlayersForTeam(601) 3 GetPlayersForSport(25) 194 GetPlayerByID(1706820) 0 GetPlayersForTeam(701) 3 GetPlayersForSport(29) 182 GetPlayerByID(1806820) 0 GetPlayersForTeam(801) 3 GetPlayersForSport(33) 191 GetPlayerByID(1906820) 0 GetPlayersForTeam(901) 3 GetPlayersForSport(37) 184 平均 7.9 3.1 353.2 总时长 3642

Dapper .Net

查询 1 Tick 查询 2 Tick 查询 3 Tick GetPlayerByID(1006820) 129 GetPlayersForTeam(1) 4 GetPlayersForSport(1) 214 GetPlayerByID(1106820) 0 GetPlayersForTeam(101) 2 GetPlayersForSport(5) 179 GetPlayerByID(1206820) 0 GetPlayersForTeam(201) 2 GetPlayersForSport(9) 176 GetPlayerByID(1306820) 0 GetPlayersForTeam(301) 2 GetPlayersForSport(13) 185 GetPlayerByID(1406820) 0 GetPlayersForTeam(401) 2 GetPlayersForSport(17) 194 GetPlayerByID(1506820) 0 GetPlayersForTeam(501) 2 GetPlayersForSport(21) 182 GetPlayerByID(1606820) 0 GetPlayersForTeam(601) 2 GetPlayersForSport(25) 189 GetPlayerByID(1706820) 0 GetPlayersForTeam(701) 9 GetPlayersForSport(29) 189 GetPlayerByID(1806820) 0 GetPlayersForTeam(801) 2 GetPlayersForSport(33) 176 GetPlayerByID(1906820) 0 GetPlayersForTeam(901) 2 GetPlayersForSport(37) 181 平均 12.9 2.9 186.5 总时长 2023

比较结果

* EF W Tracking EF WO Tracking ADO.Net Dapper.Net GetPlayerByID 180.9 176.9 7.9 12.9 GetPlayersForTeam 17 3.2 3.1 2.9 GetPlayersForSport 355.2 184.5 353.2 186.5 Total Time 5531 3646 3642 2023

感觉Dapper真的非常高效，No Tracking的EF效率也不低和ADO .Net不相上下，不过对于在系统中嵌套SQL语言，个人比较不好接受，而且不优雅，对今后维护工作也有一定麻烦，因此今后采用Dapper机会应该不多，除非一些不需要后期维护而且数据量庞大的系统或许会采用。不过仁者见仁，智者见智，当前Dapper好像非常火，但是不管用哪种方法，达到目的，而且效率不要过份低下都不失为一个好的方法。

Richard 于 2017圣诞前