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

.net平台性能很不错的轻型ORM类Dapper

dapper只有一个代码文件,完全开源,你可以放在项目里的任何位置,来实现数据到对象的ORM操作,体积小速度快。 使用ORM的好处是增、删、改很快,不用自己写sql,因为这都是重复技术含量低的工作,还...
  • mss359681091
  • mss359681091
  • 2016年07月20日 17:20
  • 3584

VS C# ADO.NET代码连接Access数据库

在Visual Studio2013 C#中以编程方式操作Access数据库,一般使用OleDb进行连接: 1.首先添加两个引用(使用using): using System.Data.OleDb; ...
  • c1481118216
  • c1481118216
  • 2016年05月31日 00:50
  • 3607

VS中修改.net版本

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

VS2008中查看.NET源码的设置方法

VS2008中查看.NET源码的设置方法
  • YaoXTao
  • YaoXTao
  • 2014年07月02日 14:59
  • 1381

vs2015开发.net core的环境准备

1、安装vs2015,并升级到update3 下载地址: http://download.microsoft.com/download/7/c/f/7cf151c3-b735-4e35-a1bb-...
  • tiana0
  • tiana0
  • 2017年01月24日 13:02
  • 4638

ADO和ADO.NET的区别

ADO.NET是ADO的后继版本,主要目的是在.NET Framework中更容易地创建分布式、 数据共享的应用程序,它提供了一个数据访问接口,以便和OLE DB数据源进行通信,如 SQL S...
  • ZHOUCHAOQIANG
  • ZHOUCHAOQIANG
  • 2014年04月30日 10:11
  • 4133

VS 使用自带的.NET Reflector单步调试编译好的程序集(反编译),以及相关其他反编译程序介绍

对于没有任何源代码和PDB文件的预编译程序集而言,如果没有合适的工具,调试起来并不容易。使用Red Gate的.NET Reflector可以在Visual Studio中即时反编译程序集,然后像...
  • AAA123524457
  • AAA123524457
  • 2016年03月04日 10:08
  • 7598

VS与MySQL通过ADO方式连接

1.下载好MySQL之后,安装开发员的版本,用命令行在数据库test下建立表st并插入了两条记录,然后OBDC添加用户、系统DSN 驱动程序是MySQL OBDC 5.3 ANSI Driver ...
  • BCD_not_CBD
  • BCD_not_CBD
  • 2015年08月29日 08:45
  • 742

VS2013下使用创建sqlite的ADO.NET实体数据模型

1. 安装DDEX(aka Data Designer Extensibility) 2. NuGet获取EF6实体最新版本以及获取System.Data.SQLite.EF6 3. 工程修改为x...
  • smilingc
  • smilingc
  • 2016年06月20日 14:39
  • 4153

如何修改vs2013所建项目的.net framework版本

 1.打开项目 找到自己以前用vs建的一个项目,用vs打开,如果是低版本的可能会有问题,不过我们今天主要讨论,怎么修改.net framework版本,所以没有太大影响...
  • yuanyuan_186
  • yuanyuan_186
  • 2016年04月21日 15:32
  • 3066
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Entity Framwork Vs ADO .Net Vs Dapper .Net 效率比较
举报原因:
原因补充:

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