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圣诞前

阅读更多
个人分类: database
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭