The second performance test of micro-ORM DapperLambda [Dapper comparison], ormdapperlambda
This week has been busy, so I have never had time to perform a performance test. I 'd like to try it out today.
Because many people are worried about performance issues, what is the performance gap between the package and Dapper? Today I will provide my Testing Method for reference only.
1 public class DBHelper 2 { 3 private static string localStr = "server=(local);User ID=sa;Password=password01!;Database=LocalDB;Persist Security Info=True;Pooling=true;Max Pool Size=700"; 4 public static DbContext GetContext() 5 { 6 return new DbContext().ConnectionString(localStr); 7 } 8 public static System.Data.IDbConnection GetDapperConnection() 9 {10 return new System.Data.SqlClient.SqlConnection(localStr);11 }12 }
2. Mainly for addition, deletion, modification, and query, and comparison, but it is relatively simple to use Dapper, all of which are SQL + parameters ..
1). Comparison of query statements
public static long DapperSelect() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var conn = DBHelper.GetDapperConnection()) { var item = conn.Query<Models.MobileForTest>("SELECT * FROM MobileForTest mft WHERE mft.ID=@ID", new { ID = r.Next(1, 3014) }); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaSQLSelect() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var context = DBHelper.GetContext()) { var item = context.Sql("SELECT * FROM MobileForTest mft WHERE mft.ID=@ID", new { ID = r.Next(1, 3014) }).QueryMany<MobileForTest>(); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaSelectByID() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var context = DBHelper.GetContext()) { var item = context.Select<MobileForTest>(r.Next(1, 3014)); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaSelectByLambda() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var context = DBHelper.GetContext()) { var item = context.Select<MobileForTest>(p => p.ID == r.Next(1, 3014)).QueryMany(); } timer.Stop(); return timer.ElapsedMilliseconds; }
Originally, four threads were scheduled to call the corresponding method 500 times in their respective threads, taking the total time consumed and discovering the thread startup sequence, which had a significant impact on the test results. Therefore, the average time is used to call each method 500 times in synchronous mode. The test results are as follows.
It is almost the same as expected.
2. Insert a single data entry
public static long DapperSQLInsert() { Stopwatch timer = new Stopwatch(); timer.Start(); using (var conn = DBHelper.GetDapperConnection()) { conn.Execute(@"INSERT INTO [dbo].[MobileForTest] ([MobileHolder] ,[MobilePhone] ,[Status]) VALUES (@MobileHolder ,@MobilePhone ,@Status)", new { MobileHolder = "InsterWithTran", MobilePhone = "18922223333", Status = 0 }); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaSQLInsert() { Stopwatch timer = new Stopwatch(); timer.Start(); using (var context = DBHelper.GetContext()) { context.Sql(@"INSERT INTO [dbo].[MobileForTest] ([MobileHolder] ,[MobilePhone] ,[Status]) VALUES (@MobileHolder ,@MobilePhone ,@Status)").Parameter("MobileHolder", "DapperLambdaSQLInsert") .Parameter("MobilePhone", "18912345678") .Parameter("Status", 0).Execute(); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaInsert() { List<MobileForTest> ls = new List<MobileForTest>(); Stopwatch timer = new Stopwatch(); timer.Start(); using (var context = DBHelper.GetContext()) { context.Insert<MobileForTest>(new MobileForTest { MobileHolder = "DapperLambdaInsert", MobilePhone = "18911112222", Status = 0 }).Execute(); } timer.Stop(); return timer.ElapsedMilliseconds; }
It is executed 500 times in a loop and the average time consumption is obtained.
3. Update Method Test
public static long DapperSQLUpdate() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var conn = DBHelper.GetDapperConnection()) { conn.Execute("UPDATE MobileForTest SET MobileHolder = @MobileHolder WHERE ID=@ID", new { MobileHolder = "DapperSQLUpdate", ID = r.Next(1, 10000) }); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaSQLUpdate() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var context = DBHelper.GetContext()) { context.Sql("UPDATE MobileForTest SET MobileHolder = @MobileHolder WHERE ID=@ID", new { MobileHolder = "DapperLambdaSQLUpdate", ID = r.Next(1, 10000) }).Execute(); } timer.Stop(); return timer.ElapsedMilliseconds; } public static long DapperLambdaUpdate() { Stopwatch timer = new Stopwatch(); timer.Start(); Random r = new Random(); using (var context = DBHelper.GetContext()) { context.Update<MobileForTest>().Set(new { MobileHolder = "DapperLambdaUpdate" }).Where(p => p.ID == r.Next(1, 10000)).Execute(); } timer.Stop(); return timer.ElapsedMilliseconds; }
In general, the test results are still in the expected scope, and you may choose between ease of use and some performance. If you have time, consider changing the Dapper and try again. Persistence ....