[C#.NET][Entity Framework] 查詢大資料性能比較
有許多的人對 EF 的查詢效能有所疑慮,其實只要使用得當,EF 的效能是很棒的,這裡並不想要挑起 ORM Solution 的戰爭,其實是自己只對 EF 熟悉(逃~),我只需要用讀取資料最快的 DataReader 方式來比較差異性,就可以用來決定是否Buy-In EF
- 本篇使用的資料庫是 http://msftdbprodsamples.codeplex.com/releases/view/105902
- 資料表 FactProductInventory,它裡面有 776286 筆
- .Net Framework 4.5
- Entity Framework 版本 6.1.3
- Windows 8.1 x64
為了方便寫測試程式碼,有了下面兩個類別
資料存取合約:
{ int RowCount { get; set; } object GetAllInventory(); }
共用 SqlConnect 物件:
{ private static string s_connectString = ConfigurationManager.ConnectionStrings["AdventureWorksDW2012DbContext"].ConnectionString; protected static SqlConnection s_connection = null; private static bool s_disposed = false; static SqlFlowDataAccessBase() { s_connection = new SqlConnection(s_connectString); s_connection.Open(); } ~SqlFlowDataAccessBase() { Dispose(false); } public void Dispose() { // Dispose of unmanaged resources. Dispose(true); // Suppress finalization. GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (s_disposed) return; if (disposing) { // Free any other managed objects here. // if (s_connection != null) { if (s_connection.State == ConnectionState.Open) { s_connection.Close(); } s_connection.Dispose(); s_connection = null; } } // Free any unmanaged objects here. // s_disposed = true; } }
Dapper 是一款高效的 ORM,我最喜歡的是它的 Dynamic Parameter 撰寫方式,個人認為它用起來比 SqlParameter 還要直覺,這是Dapper的性能比較程式碼:https://github.com/StackExchange/dapper-dot-net/blob/master/Tests/PerformanceTests.cs
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { var query = s_connection.Query<FactProductInventory>("SELECT * FROM FactProductInventory"); this.RowCount = query.Count(); return query; } }
EF 在預設情況下會 Tracking,這將耗費掉許多的資源,但這在網路資源很匱乏的時候很有用
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { using (AdventureWorksDbContext db = new AdventureWorksDbContext()) { var query = db.FactProductInventories.ToList(); this.RowCount = query.Count(); return query; } } }
使用 AsNoTracking,可得到單次查詢命令的最高效率
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { using (AdventureWorksDbContext db = new AdventureWorksDbContext()) { var query = db.FactProductInventories.AsNoTracking().ToList(); this.RowCount = query.Count(); return query; } } }
個人已經很久沒有用 DataTable 了,不過還是將它們列入比較
DataReader 不將資料存放在記憶體,我只是用它來做一個比較基準,現實上這樣寫是雞肋功能不切實際
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); int count = 0; while (reader.Read()) { object[] items = new object[reader.FieldCount]; reader.GetValues(items); count++; } this.RowCount = count; return reader; } }
SqlDataAdapter 的 Fill,將資料倒到 DataTable
核心片斷程式碼如下:
{ SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); DataTable tableReader = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(tableReader); this.RowCount = tableReader.Rows.Count; return tableReader; }
用 DataTable.Load 把資料載入
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { SqlCommand command = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); DataTable tableReader = new DataTable(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess); tableReader.Load(reader); this.RowCount = tableReader.Rows.Count; return tableReader; } }
用 DataTable.LoadDataRow 把資料載入
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); DataTable tableReader = new DataTable(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); for (int i = 0; i < reader.FieldCount; i++) tableReader.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); while (reader.Read()) { object[] items = new object[reader.FieldCount]; reader.GetValues(items); tableReader.LoadDataRow(items, true); } this.RowCount = tableReader.Rows.Count; return tableReader; } }
使用 Reflection 把 DataReader 轉成強型別,Reflection 反射效能不是很好
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); var result = ReflectRowMapping(reader); this.RowCount = result.Count; return result; } private List<FactProductInventory> ReflectRowMapping(IDataReader reader) { List<FactProductInventory> inventoryList = new List<FactProductInventory>(); var type = typeof(FactProductInventory); while (reader.Read()) { FactProductInventory inventory = new FactProductInventory(); for (int i = 0; i < reader.FieldCount; i++) { var fieldName = reader.GetName(i); var value = reader.GetValue(i); PropertyInfo property = inventory.GetType().GetProperty(fieldName); property.SetValue(inventory, reader.IsDBNull(i) ? "null" : value); } inventoryList.Add(inventory); } return inventoryList; } }
使用 Expression 把 DataReader 轉成強型別
核心片斷程式碼如下:
{ public int RowCount { get; set; } public object GetAllInventory() { SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); var result = DynamicRowMapping(reader); this.RowCount = result.Count; return result; } private List<FactProductInventory> DynamicRowMapping(IDataReader reader) { List<FactProductInventory> inventoryList = new List<FactProductInventory>(); var mapping = new DynamicProperty<FactProductInventory>(); while (reader.Read()) { FactProductInventory inventory = new FactProductInventory(); for (int i = 0; i < reader.FieldCount; i++) { var fieldName = reader.GetName(i); var value = reader.GetValue(i); mapping.SetValue(inventory, fieldName, value); } inventoryList.Add(inventory); } return inventoryList; } }
測試片斷程式碼
public void TestMethod1() { var runTimes = 1; IFlowDataAccess dapper = new DapperDAO(); IFlowDataAccess ef = new EntityDAO(); IFlowDataAccess efNoTrack = new EntityNoTrackDAO(); IFlowDataAccess dataReaderLoad = new DataReaderLoadDAO(); IFlowDataAccess dataReaderLoadDataRow = new DataReaderLoadDataRowDAO(); IFlowDataAccess dataReaderDynamicMappingDAO = new DataReaderExpressionMappingDAO(); IFlowDataAccess dataReaderReflectMappingDAO = new DataReaderReflectMappingDAO(); IFlowDataAccess dataReaderDAO = new DataReaderDAO(); IFlowDataAccess dataAdapterDAO = new DataAdapterDAO(); var test1 = new TestInfo(() => { var datas = dapper.GetAllInventory(); return dapper; }, "Dapper"); test1.Run(runTimes); var test2 = new TestInfo(() => { var datas = ef.GetAllInventory(); return ef; }, "EF"); test2.Run(runTimes); var test3 = new TestInfo(() => { var datas = efNoTrack.GetAllInventory(); return efNoTrack; }, "EF No Track"); test3.Run(runTimes); var test4 = new TestInfo(() => { var datas = dataReaderLoad.GetAllInventory(); return dataReaderLoad; }, "DataReader for DataTable.Load"); test4.Run(runTimes); var test5 = new TestInfo(() => { var datas = dataReaderLoadDataRow.GetAllInventory(); return dataReaderLoadDataRow; }, "DataReader for DataTable.LoadDataRow"); test5.Run(runTimes); var test6 = new TestInfo(() => { var datas = dataReaderDynamicMappingDAO.GetAllInventory(); return dataReaderDynamicMappingDAO; }, "DataReader for Expression Mapping"); test6.Run(runTimes); var test7 = new TestInfo(() => { var datas = dataReaderReflectMappingDAO.GetAllInventory(); return dataReaderReflectMappingDAO; }, "DataReader for Reflect Mapping"); test7.Run(runTimes); var test8 = new TestInfo(() => { var datas = dataReaderDAO.GetAllInventory(); return dataReaderDAO; }, "DataReader"); test8.Run(runTimes); var test9 = new TestInfo(() => { var datas = dataAdapterDAO.GetAllInventory(); return dataAdapterDAO; }, "DataAdapter use Fill"); test9.Run(runTimes); Console.Write(""); Console.WriteLine("排名,最快的在前面:"); List<TestInfo> testInfos = new List<TestInfo>() { test1, test2, test3, test4, test5, test6, test7, test8, test9 }; var storts = testInfos.OrderBy(t => t.CostTime); var index = 1; foreach (var info in storts) { Console.WriteLine("第{0}名,{1}", index, info.Message); index++; } }
上述方法會用到 TestInfo 類別
執行測試結果:
跑一次還不夠,跑個10次看看
結論:
- 強型別在 Vistual Studio 有 Intellisense,可幫助我們在開發階段提示錯誤,弱型別當然就沒有,有人說弱型別好寫,我個人認為那不叫好寫,而叫鬆散,鬆散所帶來的副作用,就是後續引發除錯成本是很高的
- 除了 EF 之外,其餘的寫法都不是使用強型別物件操作,而是弱型別的字串,這是天差地遠的撰寫方式,硬是要把 Dapper | ADO.NET 跟 EF 擺在一塊比,對 EF 不公平,畢竟 EF 提供的功能比它們還要多很多;
- 它能有單次查詢高效能的表現,但也能有最低效能的表現,全看你的需求決定是否需要 Tracking
- Expression 的強型別對應已經直逼 Dapper 與 EF,未來會將這段程式碼收錄下來
- 若不能用 EF,Dapper 會是我處理資料庫的首選
- DataAdapter 居然可以比 DataReader 快
為什麼我不用 DataTable:
- 它預設是弱型別
- 雖然它可以有強型別(DataSet),它的物件操作方式與一般物件不同,對我來講不夠直覺,參考 http://www.dotblogs.com.tw/yc421206/archive/2014/07/14/145944.aspx
- 較耗損網路資源
本文出自:http://www.dotblogs.com.tw/yc421206/archive/2015/03/16/150743.aspx
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET