[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
