新玩具 linq2db 入手，一定要拿來測測效能的，我準備了三張表，裡面各有一百萬筆，有兩種讀取方式，一個是讀取某一張表，一個是Join兩張表
開發環境
- VS 2017 15.9.5
- .Net Framework 4.7.2
- Entity Framework 6.2.0
- Dapper 1.60.1
- linq2db 2.6.4
實作
為了方便測試，建立了一個接口，為了要把 ADO.NET 也一併納進來測試，用到了 DataTable
public interface IEmployeeRepository
{
string ConnectionName { get; set; }
object GetAll(out int count);
IEnumerable<EmployeeViewModel> GetAllEmployees(out int count);
IEnumerable<EmployeeViewModel> GetAllEmployeesDetail(out int count);
}
public interface IAdoEmployeeRepository
{
string ConnectionName { get; set; }
DataTable GetAllEmployees(out int count);
DataTable GetAllEmployeesDetail(out int count);
}
EF
- 因為別的框架沒有追蹤，比較時，也應該要關掉，這樣才公平
- LazyLoadingEnabled 、ProxyCreationEnabled：關閉消極式載入。
- AutoDetectChangesEnabled ：停用異動追蹤，不過這次的情境並不會用到它。
- AsNoTracking：關閉追蹤，使用查詢最關鍵的一個指令，只要查詢後沒有要立即異動，都要關掉。
public IEnumerable<EmployeeViewModel> GetAllEmployees(out int count)
{
IEnumerable<EmployeeViewModel> results = null;
using (var dbContext = new LabDbContext(this.ConnectionName))
{
dbContext.Configuration.LazyLoadingEnabled = false;
dbContext.Configuration.ProxyCreationEnabled = false;
dbContext.Configuration.AutoDetectChangesEnabled = false;
var selector = dbContext.Employees
.Select(p => new EmployeeViewModel
{
Id = p.Id,
Name = p.Name,
Age = p.Age,
SequenceId = p.SequenceId,
});
results = selector.AsNoTracking().ToList();
count = results.Count();
}
return results;
}
Linq2Db
因為它沒有狀態追蹤，所以看起來乾淨了一些
public IEnumerable<EmployeeViewModel> GetAllEmployees(out int count)
{
IEnumerable<EmployeeViewModel> results = null;
using (var db = new LabEmployeeDB(this.ConnectionName))
{
var selector = db.Employees
.Select(p => new EmployeeViewModel
{
Id = p.Id,
Name = p.Name,
Age = p.Age,
SequenceId = p.SequenceId
})
.Where(p=>p.SequenceId>0)
.OrderBy(p=>p.SequenceId);
results = selector.ToList();
count = results.Count();
}
return results;
}
Dapper
public IEnumerable<EmployeeViewModel> GetAllEmployees(out int count)
{
IEnumerable<EmployeeViewModel> results = null;
count = 0;
using (var db = DbManager.CreateConnection(this.ConnectionName))
{
results = db.Query<EmployeeViewModel>(SqlEmployeeText.AllEmployee);
count = results.Count();
}
return results;
}
查詢腳本 ADO.NET 跟 Dapper 都用相同的寫法
class SqlEmployeeText
{
public static readonly string AllEmployee = @"
SELECT
[p].[Id],
[p].[Name],
[p].[Age],
[p].[SequenceId]
FROM
[dbo].[Employee] [p]
WHERE
[p].[SequenceId] > 0
ORDER BY
[p].[SequenceId]
";
public static readonly string EmployeeCount = "SELECT COUNT(1) FROM Employee";
}
DataReader 轉 DataTable
public DataTable GetAllEmployees(out int count)
{
DataTable result = null;
using (var dbConnection = DbManager.CreateConnection(this.ConnectionName))
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = SqlEmployeeText.AllEmployee;
var reader = dbCommand.ExecuteReader(CommandBehavior.SequentialAccess);
result = ToDataTable(reader);
count = result.Rows.Count;
}
return result;
}
只要不轉 DataTable 他是最快的，但不是快就好，後面還要拿出來用
Adapter.Fill
public DataTable GetAllEmployees(out int count)
{
DataTable result = null;
using (var dbConnection = DbManager.CreateConnection(this.ConnectionName))
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = SqlEmployeeText.AllEmployee;
result = this.ExecuteDataTable(dbCommand);
count = result.Rows.Count;
}
return result;
}
LoadDataRow
public DataTable GetAllEmployees(out int count)
{
DataTable result = null;
using (var dbConnection = DbManager.CreateConnection(this.ConnectionName))
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = SqlEmployeeText.AllEmployee;
var reader = dbCommand.ExecuteReader(CommandBehavior.SequentialAccess);
result = ToDataTable(reader);
count = result.Rows.Count;
}
return result;
}
Load
public DataTable GetAllEmployees(out int count)
{
DataTable result = null;
using (var dbConnection = DbManager.CreateConnection(this.ConnectionName))
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = SqlEmployeeText.AllEmployee;
var reader = dbCommand.ExecuteReader(CommandBehavior.SequentialAccess);
result = new DataTable();
result.Load(reader);
count = result.Rows.Count;
}
return result;
}
測試程式
建構函數
- 我把所有的 repository 放在集合，然後跑迴圈呼叫 BenchmarkManager.Add 傳入需要量測的 Method。
- 關閉 EF 的檢查
- 先載入 EF 的 Entity Model 對應
- 暖機一下，主要是在排除第一次執行所花費的時間，大家先跑一下
- 暖機的 db 是小量資料只放一筆，暖完之後就切到一百萬筆的連線字串
[TestClass]
public class BenchmarkTest
{
private static readonly BenchmarkManager BenchmarkManager = new BenchmarkManager();
static BenchmarkTest()
{
string connectionName = "LabDbContext";
//呼叫 BenchmarkManager.Add 傳入需要量測的 Method
foreach (var repository in Utility.AdoRepositories)
{
BenchmarkManager.Add(repository.Key.ToString(),
() =>
{
var employees = repository.Value.GetAllEmployees(out var count);
return new Report {RowCount = count};
});
}
foreach (var repository in Utility.Repositories)
{
BenchmarkManager.Add(repository.Key.ToString(),
() =>
{
var employees = repository.Value.GetAllEmployees(out var count);
return new Report {RowCount = count};
});
}
//不檢查migration table
Database.SetInitializer<LabDbContext>(null);
//載入對應
using (var dbcontext = new LabDbContext(connectionName))
{
var objectContext = ((IObjectContextAdapter) dbcontext).ObjectContext;
var mappingCollection =
(StorageMappingItemCollection) objectContext.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace);
mappingCollection.GenerateViews(new List<EdmSchemaError>());
}
//暖機
BenchmarkManager.Warm();
Utility.Warm();
Utility.SwitchLargeDb();
}
[TestMethod]
public void Benchmark_Statistics_10()
{
BenchmarkManager.Statistics(10);
}
}
BenchmarkManager.cs
用 Stopwatch 量測執行時間，這裡有用到 Console Table，請參考
https://stackoverflow.com/questions/856845/how-to-best-way-to-draw-table-in-console-app-c
把要量測的方法放進 s_targets 集合
public void Add(string name, Func<Report> target)
{
if (s_targets.ContainsKey(name))
{
s_targets[name] = target;
}
else
{
s_targets.Add(name, target);
}
}
量測，要量測的東西由外部程試跟他說，要先調用 Add
public List<Report> Statistics(int count = 1)
{
var firstReports = new Dictionary<string, List<Report>>();
var currentCount = count;
var watch = new Stopwatch();
foreach (var target in s_targets)
{
var index = 1;
while (count-- > 0)
{
watch.Restart();
var report = target.Value.Invoke();
watch.Stop();
report.CostTime = watch.Elapsed.TotalMilliseconds;
report.Name = target.Key;
report.Index = index;
if (firstReports.ContainsKey(target.Key) == false)
{
firstReports.Add(target.Key, new List<Report> {report});
}
else
{
firstReports[target.Key].Add(report);
}
index++;
}
count = currentCount;
}
var totalReports = GenerateTotalReports(firstReports);
var detailReports = GenerateDetailReport(totalReports);
return totalReports;
}
一次一百萬，量測10次
可以看到三套 ORM，相差無幾，可視為相等，不過最令我驚訝的是 EF 的 Tracking，我記得 Tracking 會花很多時間阿，這個版本似乎跟以前不一樣，找機會再來研究研究
Join 兩張一百萬筆的資料，量測10次
專案內有我這次測試的資料結構，然後我用 dbForge Data Generator for SQL Server 分別產出一筆及一百萬筆的資料庫
範例專案
https://github.com/yaochangyu/sample.dotblog/tree/master/ORM/ORM.Benchmark
效能測試
https://dotblogs.com.tw/yc421206/2019/03/13/query_large_data_compare_ef_dapper_linq2db
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
