测试环境:
硬件: Intel I5 4核 +8G内存。
软件: Windows 7 + vs2013 SP2 + EF6.1 + MVC5.1
数据库: vs2013自带的sql express 2012.
测试过程:
1. 新建一默认模板的MVC项目,在Models中添加如下代码并建立对应的库表:
[Table("Person")] public class Person { public int Id { get; set; } public string UserName { get; set; } public DateTime RegTime { get; set; } public DateTime Birthdate { get; set; } public string Mark { get; set; } }
这里有一个有趣的插曲,开始报错找不到“ado.people",纳闷呢,怎么EF这么”聪明“,自动地认为表名是和Person对应的People?
不管了,用[Table]的属性强制固定住。
2. 在HomeController里新建两个Action,一个是管插入Person的,一个是查询Person的:
using EFXpoAdoCompare.Models; using System; using System.Collections.Generic; using System.Data.Common; using System.Data.SqlClient; using System.Diagnostics; using System.Linq; using System.Web; using System.Web.Mvc; namespace EFXpoAdoCompare.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } public ActionResult About() { ViewBag.Message = "Your application description page."; return View(); } public ActionResult Contact() { ViewBag.Message = "Your contact page."; return View(); } /// <summary> /// 使用EF保存数据 /// </summary> /// <param name="persons"></param> void EFSave(Person[] persons) { ApplicationDbContext db = new ApplicationDbContext(); db.Persons.AddRange(persons); db.SaveChanges(); } /// <summary> /// 保用ado保存数据 /// </summary> /// <param name="persons"></param> void AdoSave(Person[] persons) { SqlConnection conn = new ApplicationDbContext().Database.Connection as SqlConnection; conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(@"INSERT INTO Person (Birthdate,RegTime,UserName,Mark) VALUES(@Birthdate,@RegTime,@UserName,@Mark)", conn, trans); cmd.Parameters.Add("Birthdate", System.Data.SqlDbType.Date); cmd.Parameters.Add("RegTime", System.Data.SqlDbType.DateTime); cmd.Parameters.Add("UserName", System.Data.SqlDbType.NVarChar); cmd.Parameters.Add("Mark", System.Data.SqlDbType.NVarChar); foreach (var p in persons) { cmd.Parameters["Birthdate"].Value = p.Birthdate; cmd.Parameters["RegTime"].Value = p.RegTime; cmd.Parameters["UserName"].Value = p.UserName; cmd.Parameters["Mark"].Value = p.Mark; cmd.ExecuteNonQuery(); } trans.Commit(); } conn.Close(); } /// <summary> /// EF查询数据 /// </summary> /// <param name="persons"></param> void EFSearch(Person[] persons) { ApplicationDbContext db = new ApplicationDbContext(); var list = db.Persons.Where(p => p.UserName.CompareTo("d") >= 1).Take(_num).ToList(); ViewBag.List = list; } /// <summary> /// Ado查询数据 /// </summary> /// <param name="persons"></param> void AdoSearch(Person[] persons) { SqlConnection conn = new ApplicationDbContext().Database.Connection as SqlConnection; conn.Open(); string sql = "SELECT TOP " + _num + " * FROM Person WHERE UserName >= ‘d‘"; SqlCommand comm = new SqlCommand(sql, conn); List<Person> ps = new List<Person>(); using (SqlDataReader reader = comm.ExecuteReader()) { while (reader.Read()) { ps.Add(new Person() { Birthdate = (DateTime)reader["Birthdate"], Id = (int)reader["Id"], UserName = (string)reader["UserName"], Mark = (string)reader["Mark"], RegTime = (DateTime)reader["RegTime"], }); } } ViewBag.List = ps; conn.Close(); } public ActionResult SearchPerson(int? num) { if (num == null) num = 1000; _num = num.Value; PersonsCreater pc = new PersonsCreater(_num); pc.OnPersonsSaving = AdoSearch; ViewBag.AdoMS = pc.SavePersons(); pc.OnPersonsSaving = EFSearch; ViewBag.EFMS = pc.SavePersons(); return View(); } int _num; public ActionResult CreatePerson(int? num) { if (num == null) num = 1000; _num = num.Value; PersonsCreater pc = new PersonsCreater(_num); pc.OnPersonsSaving = AdoSave; ViewBag.AdoMS = pc.SavePersons(); pc.OnPersonsSaving = EFSave; ViewBag.EFMS = pc.SavePersons(); return View(); } } class PersonsCreater { int _num; public PersonsCreater(int num) { _num = num; } public Action<Person[]> OnPersonsSaving; Person[] CreatePersons() { Person[] persons = new Person[_num]; Random rand = new Random(); for (int i = 0; i < _num; i++) { persons[i] = new Person() { Birthdate = DateTime.Today.AddDays(-rand.Next(365 * 20, 365 * 50)), RegTime = DateTime.Now.AddSeconds(-rand.Next(0, 86400 * 365)), UserName = Guid.NewGuid().ToString(), Mark = "My Mark:" + Guid.NewGuid().ToString(), }; } return persons; } Stopwatch sw = new Stopwatch(); public long SavePersons() { var persons = CreatePersons(); sw.Restart(); OnPersonsSaving(persons); sw.Stop(); return sw.ElapsedMilliseconds; } } }
3.添加视图,配置路由(此处暂且不表,重点不在这儿)试运行通过。
4.开始测试:
测试结果(插入数据, 单位:ms):
条数 | 100 | 1000 | 3000 | 10000 | 100000 |
EF | 31 | 196 | 649 | 2955 | 22714 |
Ado | 12 | 128 | 245 | 1044 | 11051 |
测试结果(查询数据, 单位 ms):
条数 | 100 | 1000 | 3000 | 10000 | 100000 |
EF | 4 | 11 | 24 | 50 | 522 |
Ado | 3 | 6 | 13 | 30 | 169 |
我先用debug版本,在vs环境下调试,结果比Release版本不调试,速度至少慢了10倍。所以大家在生产环境部署时,一定要用Relase编译你的程序,切记切记。
以上数据都是Release下的运行结果。
在以上查询数据的结果中, 数据量越小,测试结果越不规则,有时甚至出现ef比ado还快的情况。
测试结论: 在通常的小数据量应用中,EF和Ado的差别可以忽略不计。只有在大规模插入数据时,EF性能才会明显落后于传统ado。