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

原创 2017年12月11日 09:27:53

由我另一个博客拷贝
近年来一直使用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圣诞前

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

近年来一直使用EF+.Net MVC来编写系统,由于最近换了一个公司,碰到一个印度同事,他告诉我用Dapper比用EF高效很多,基本可以接近ADO .Net 的效率,因为习惯的问题一直没有对ORM做深...
  • qq_40503124
  • qq_40503124
  • 2017年12月09日 01:27
  • 137

ADO.NET Entity Framework与ADO.NET的区别

两者的区别: 1、采用EF进行开发只需要操作对象。使开发更对象化,抛弃了数据库中心的思想,完全的面向对象思想。ADO.NET以数据库为中心来开发数据访问层。 2、采用EF减少了数据处理工作,可以简...
  • wangzl1163
  • wangzl1163
  • 2017年06月07日 10:52
  • 1950

core 1.1 + vs 2017 +mysql +dapper

今天做了一下core 的测试 core 1.1 + vs 2017 +mysql +dapper 简单的三层架构 结构如图 startup 接口 实现层 目前只...
  • limeng12319
  • limeng12319
  • 2016年11月30日 18:49
  • 1349

VS2010调试.net framework源码

原文地址:http://blog.csdn.net/waxgourd0/article/details/6600182
  • XuWei_XuWei
  • XuWei_XuWei
  • 2014年05月20日 21:31
  • 1125

VS中修改.net版本

更换.net版本,重新build。 网站项目分为 website 和 web application project 两种开发模式 1)若为website 项目名右键Property pages,...
  • jl1134069094
  • jl1134069094
  • 2016年04月14日 14:38
  • 3190

mybatis.net 和 dapper.net 性能比较

一、mybatis.net 和 dapper.net 插入数据性能比较(毫秒),连续执行4次的结果 插入比较(y/n):y Mybatis.net 插入 50 条记录耗时:4630 dapper.n...
  • xxj_jing
  • xxj_jing
  • 2016年06月08日 17:58
  • 3710

.net core 基于 dapper 扩展的操作 mysql

public class MySQLDBHelper     {         private string connString = "";         public MySQLDBH...
  • MeGoodtoo
  • MeGoodtoo
  • 2017年06月19日 11:36
  • 833

ASP .Net Core 使用 Dapper 轻型ORM框架

一:优势 1,Dapper是一个轻型的ORM类。代码就一个SqlMapper.cs文件,编译后就40K的一个很小的Dll. 2,Dapper很快。Dapper的速度接近与IDataRead...
  • xwnxwn
  • xwnxwn
  • 2016年12月23日 17:39
  • 2299

.netframework 4.5.1安装成功,单在vs目标框架中找不到

安装好vs2012后默认的目标框架是.netframework 4.5, 现在想安装4.5.1的,已经提示安装成功,并且通过aspnet_regiis.exe注册过了, 通过aspnet_regiis...
  • u013236043
  • u013236043
  • 2017年06月29日 09:56
  • 1548

GO,NODEJS,.NetCore性能对比

测试电脑配置RAM:8G CPU:amd A8-5600K 3.6GH OS:Win10开发语言版本GO:1.9 Nodejs:8.1.2 .NetCore:2.0测试场景循环50亿次并计算5...
  • fwj380891124
  • fwj380891124
  • 2017年08月29日 16:28
  • 1620
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Entity Framwork Vs ADO .Net Vs Dapper .Net 效率比较
举报原因:
原因补充:

(最多只允许输入30个字)