Dapper, Ef core, Freesql 插入大量数据性能比较（二）
在上一篇文章中，我们比较出单表插入9999行数据，Freesql > Dapper > EfCore。在本文中，我们来看看级联插入
构建9999行数据
List<Entity> datas = new List<Entity>(); for (int i = 0; i < 9999; i++) { var item = new Entity { No = i + 1, Col1 = Guid.NewGuid().ToString("N"), Col2 = Guid.NewGuid().ToString("N"), Col3 = Guid.NewGuid().ToString("N"), Col4 = Guid.NewGuid().ToString("N"), Col5 = Guid.NewGuid().ToString("N"), Col6 = Guid.NewGuid().ToString("N"), Col7 = Guid.NewGuid().ToString("N"), Col8 = Guid.NewGuid().ToString("N"), Col9 = Guid.NewGuid().ToString("N"), Col10 = Guid.NewGuid().ToString("N"), }; item.EntitySubs.Add(new EntitySub { Col1 = Guid.NewGuid().ToString("N"), Col2 = Guid.NewGuid().ToString("N"), Col3 = Guid.NewGuid().ToString("N"), Col4 = Guid.NewGuid().ToString("N"), Col5 = Guid.NewGuid().ToString("N"), Col6 = Guid.NewGuid().ToString("N"), Col7 = Guid.NewGuid().ToString("N"), Col8 = Guid.NewGuid().ToString("N"), Col9 = Guid.NewGuid().ToString("N"), Col10 = Guid.NewGuid().ToString("N"), }); item.EntitySubs.Add(new EntitySub { Col1 = Guid.NewGuid().ToString("N"), Col2 = Guid.NewGuid().ToString("N"), Col3 = Guid.NewGuid().ToString("N"), Col4 = Guid.NewGuid().ToString("N"), Col5 = Guid.NewGuid().ToString("N"), Col6 = Guid.NewGuid().ToString("N"), Col7 = Guid.NewGuid().ToString("N"), Col8 = Guid.NewGuid().ToString("N"), Col9 = Guid.NewGuid().ToString("N"), Col10 = Guid.NewGuid().ToString("N"), }); item.EntitySubs.Add(new EntitySub { Col1 = Guid.NewGuid().ToString("N"), Col2 = Guid.NewGuid().ToString("N"), Col3 = Guid.NewGuid().ToString("N"), Col4 = Guid.NewGuid().ToString("N"), Col5 = Guid.NewGuid().ToString("N"), Col6 = Guid.NewGuid().ToString("N"), Col7 = Guid.NewGuid().ToString("N"), Col8 = Guid.NewGuid().ToString("N"), Col9 = Guid.NewGuid().ToString("N"), Col10 = Guid.NewGuid().ToString("N"), }); datas.Add(item); }
Dapper：
static void AddDataByDapperCascade(List<Entity> datas) { #region 数据格式转换 var dataTemporarys = new List<EntityTemporary>(); var dataSubTemporarys = new List<EntitySubTemporary>(); for (int i = 0, length = datas.Count; i < length; i++) { var item = datas[i]; var newItem = new EntityTemporary { No = item.No, Col1 = item.Col1, Col2 = item.Col2, Col3 = item.Col3, Col4 = item.Col4, Col5 = item.Col5, Col6 = item.Col6, Col7 = item.Col7, Col8 = item.Col8, Col9 = item.Col9, Col10 = item.Col10, Position = i + 1 }; dataTemporarys.Add(newItem); dataSubTemporarys.AddRange(item.EntitySubs.Select(x => new EntitySubTemporary { Col1 = x.Col1, Col2 = x.Col2, Col3 = x.Col3, Col4 = x.Col4, Col5 = x.Col5, Col6 = x.Col6, Col7 = x.Col7, Col8 = x.Col8, Col9 = x.Col9, Col10 = x.Col10, Position = i + 1 })); } #endregion Stopwatch sw = new Stopwatch(); sw.Start(); using (var conn = new SqlConnection(connString)) { conn.Open(); string createTable = @"create table #EntityTemp ([No] int, Col1 varchar(50), Col2 varchar(50), Col3 varchar(50), Col4 varchar(50), Col5 varchar(50), Col6 varchar(50), Col7 varchar(50), Col8 varchar(50), Col9 varchar(50), Col10 varchar(50), Position int);"; string createTable2 = @"create table #EntitySubTemp (Col1 varchar(50), Col2 varchar(50), Col3 varchar(50), Col4 varchar(50), Col5 varchar(50), Col6 varchar(50), Col7 varchar(50), Col8 varchar(50), Col9 varchar(50), Col10 varchar(50), Position int);"; string insertTable = "INSERT INTO #EntityTemp ([No], Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Position) VALUES (@No, @Col1, @Col2, @Col3, @Col4, @Col5, @Col6, @Col7, @Col8, @Col9, @Col10, @Position)"; string insertTable2 = "INSERT INTO #EntitySubTemp (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Position) VALUES (@Col1, @Col2, @Col3, @Col4, @Col5, @Col6, @Col7, @Col8, @Col9, @Col10, @Position)"; string saveSql = @"DECLARE @inserted0 TABLE ([Id] int, [Position] [int]); MERGE into TestAddSortByDapper t USING #EntityTemp AS s ON 1=0 WHEN NOT MATCHED THEN INSERT([No], [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10]) VALUES(s.[No], s.[Col1], s.[Col2], s.[Col3], s.[Col4], s.[Col5], s.[Col6], s.[Col7], s.[Col8], s.[Col9], s.[Col10]) OUTPUT INSERTED.[Id], s.Position INTO @inserted0; INSERT INTO TestAddSortByDapperSub ([Id2], [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10]) SELECT i.id AS id2, s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6, s.Col7, s.Col8, s.Col9, s.Col10 FROM #EntitySubTemp s INNER JOIN @inserted0 i ON s.[Position] = i.[Position];"; conn.Execute(createTable + " \r\n " + createTable2); conn.Execute(insertTable, dataTemporarys); conn.Execute(insertTable2, dataSubTemporarys); conn.Execute(saveSql); } sw.Stop(); Console.WriteLine($"通过 Dapper和临时表进行insert操作 毫时{sw.ElapsedMilliseconds}"); }
执行结果总结
数据库执行结果也和我们sql代码一样，dapper也是用insert into table() values () 的方法一行行加代码，执行时间大概在6-7秒。
EfCore：
由于efcore本身就支持级联增加，所有代码比较简单
public class TestContext : DbContext { public DbSet<Entity> Entity { get; set; } public DbSet<EntitySub> EntitySub { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(connString); } } [Table("TestAddSortByEfCore")] public class Entity { public int Id { get; set; } public int No { get; set; } public string Col1 { get; set; } public string Col2 { get; set; } public string Col3 { get; set; } public string Col4 { get; set; } public string Col5 { get; set; } public string Col6 { get; set; } public string Col7 { get; set; } public string Col8 { get; set; } public string Col9 { get; set; } public string Col10 { get; set; } [ForeignKey("Id2")] public virtual ICollection<EntitySub> EntitySubs { get; set; } = new HashSet<EntitySub>(); } [Table("TestAddSortByEfCoreSub")] public class EntitySub { public int Id { get; set; } public int Id2 { get; set; } public string Col1 { get; set; } public string Col2 { get; set; } public string Col3 { get; set; } public string Col4 { get; set; } public string Col5 { get; set; } public string Col6 { get; set; } public string Col7 { get; set; } public string Col8 { get; set; } public string Col9 { get; set; } public string Col10 { get; set; } } static void AddDataByEfCoreCascade(List<Entity> datas) { int r1 = 0; Stopwatch sw = new Stopwatch(); sw.Start(); using (var db = new TestContext()) { db.Entity.AddRange(datas); r1 = db.SaveChanges(); } sw.Stop(); Console.WriteLine($"通过 EfCore 导入数据{r1}行 毫时{sw.ElapsedMilliseconds}"); }
执行结果总结
-- 数据库实际执行语句 (@p0 nvarchar(4000),...,@p461 int) SET NOCOUNT ON; DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]); MERGE [TestAddSortByEfCore] USING ( VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, 0),...,(@p451, @p452, @p453, @p454, @p455, @p456, @p457, @p458, @p459, @p460, @p461, 41) ) AS i ([Col1], [Col10], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [No], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Col1], [Col10], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [No]) VALUES (i.[Col1], i.[Col10], i.[Col2], i.[Col3], i.[Col4], i.[Col5], i.[Col6], i.[Col7], i.[Col8], i.[Col9], i.[No]) OUTPUT INSERTED.[Id], i._Position INTO @inserted0; SELECT [t].[Id] FROM [TestAddSortByEfCore] t INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]) ORDER BY [i].[_Position];
(@p11 nvarchar(4000),...,@p471 nvarchar(4000),@p472 int) SET NOCOUNT ON; DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]); MERGE [TestAddSortByEfCoreSub] USING ( VALUES (@p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, 0),...,(@p462, @p463, @p464, @p465, @p466, @p467, @p468, @p469, @p470, @p471, @p472, 41) ) AS i ([Col1], [Col10], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Id2], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Col1], [Col10], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Id2]) VALUES (i.[Col1], i.[Col10], i.[Col2], i.[Col3], i.[Col4], i.[Col5], i.[Col6], i.[Col7], i.[Col8], i.[Col9], i.[Id2]) OUTPUT INSERTED.[Id], i._Position INTO @inserted0; SELECT [t].[Id] FROM [TestAddSortByEfCoreSub] t INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]) ORDER BY [i].[_Position];
从结果我们可以看到，efcore使用的是Merge方式执行，总耗时14-15秒，这个在性能上是不能接受的。
ADO.Net BulkCopy：
static void AddDataByBulkCopyTemporary2(List<Entity> datas) { Stopwatch sw = new Stopwatch(); sw.Start(); int r = 0; using (SqlConnection cn = new SqlConnection(connString)) { cn.Open(); DataTable entityTable = new DataTable(); entityTable.Columns.Add("No"); entityTable.Columns.Add("Col1"); entityTable.Columns.Add("Col2"); entityTable.Columns.Add("Col3"); entityTable.Columns.Add("Col4"); entityTable.Columns.Add("Col5"); entityTable.Columns.Add("Col6"); entityTable.Columns.Add("Col7"); entityTable.Columns.Add("Col8"); entityTable.Columns.Add("Col9"); entityTable.Columns.Add("Col10"); entityTable.Columns.Add("Position"); DataTable entitySubTable = new DataTable(); entitySubTable.Columns.Add("Col1"); entitySubTable.Columns.Add("Col2"); entitySubTable.Columns.Add("Col3"); entitySubTable.Columns.Add("Col4"); entitySubTable.Columns.Add("Col5"); entitySubTable.Columns.Add("Col6"); entitySubTable.Columns.Add("Col7"); entitySubTable.Columns.Add("Col8"); entitySubTable.Columns.Add("Col9"); entitySubTable.Columns.Add("Col10"); entitySubTable.Columns.Add("Position"); for (int i = 0; i < datas.Count; i++) { var item = datas[i]; DataRow dr = entityTable.NewRow(); dr[0] = item.No; dr[1] = item.Col1; dr[2] = item.Col2; dr[3] = item.Col3; dr[4] = item.Col4; dr[5] = item.Col5; dr[6] = item.Col6; dr[7] = item.Col7; dr[8] = item.Col8; dr[9] = item.Col9; dr[10] = item.Col10; dr[11] = i + 1; entityTable.Rows.Add(dr); foreach (var sub in item.EntitySubs) { var subDr = entitySubTable.NewRow(); subDr[0] = sub.Col1; subDr[1] = sub.Col2; subDr[2] = sub.Col3; subDr[3] = sub.Col4; subDr[4] = sub.Col5; subDr[5] = sub.Col6; subDr[6] = sub.Col7; subDr[7] = sub.Col8; subDr[8] = sub.Col9; subDr[9] = sub.Col10; subDr[10] = i + 1; entitySubTable.Rows.Add(subDr); } } string createTable = @"create table #EntityTemp ([No] int, Col1 varchar(50), Col2 varchar(50), Col3 varchar(50), Col4 varchar(50), Col5 varchar(50), Col6 varchar(50), Col7 varchar(50), Col8 varchar(50), Col9 varchar(50), Col10 varchar(50), Position int);"; string createTable2 = @"create table #EntitySubTemp (Col1 varchar(50), Col2 varchar(50), Col3 varchar(50), Col4 varchar(50), Col5 varchar(50), Col6 varchar(50), Col7 varchar(50), Col8 varchar(50), Col9 varchar(50), Col10 varchar(50), Position int);"; cn.Execute(createTable); cn.Execute(createTable2); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(cn)) { sqlBulkCopy.BatchSize = entityTable.Rows.Count; sqlBulkCopy.BulkCopyTimeout = 1800; sqlBulkCopy.DestinationTableName = "#EntityTemp"; sqlBulkCopy.ColumnMappings.Add("No", "No"); sqlBulkCopy.ColumnMappings.Add("Col1", "Col1"); sqlBulkCopy.ColumnMappings.Add("Col2", "Col2"); sqlBulkCopy.ColumnMappings.Add("Col3", "Col3"); sqlBulkCopy.ColumnMappings.Add("Col4", "Col4"); sqlBulkCopy.ColumnMappings.Add("Col5", "Col5"); sqlBulkCopy.ColumnMappings.Add("Col6", "Col6"); sqlBulkCopy.ColumnMappings.Add("Col7", "Col7"); sqlBulkCopy.ColumnMappings.Add("Col8", "Col8"); sqlBulkCopy.ColumnMappings.Add("Col9", "Col9"); sqlBulkCopy.ColumnMappings.Add("Col10", "Col10"); sqlBulkCopy.ColumnMappings.Add("Position", "Position"); sqlBulkCopy.WriteToServer(entityTable); } using (SqlBulkCopy sqlBulkCopy2 = new SqlBulkCopy(cn)) { sqlBulkCopy2.BatchSize = entitySubTable.Rows.Count; sqlBulkCopy2.BulkCopyTimeout = 1800; sqlBulkCopy2.DestinationTableName = "#EntitySubTemp"; sqlBulkCopy2.ColumnMappings.Add("Col1", "Col1"); sqlBulkCopy2.ColumnMappings.Add("Col2", "Col2"); sqlBulkCopy2.ColumnMappings.Add("Col3", "Col3"); sqlBulkCopy2.ColumnMappings.Add("Col4", "Col4"); sqlBulkCopy2.ColumnMappings.Add("Col5", "Col5"); sqlBulkCopy2.ColumnMappings.Add("Col6", "Col6"); sqlBulkCopy2.ColumnMappings.Add("Col7", "Col7"); sqlBulkCopy2.ColumnMappings.Add("Col8", "Col8"); sqlBulkCopy2.ColumnMappings.Add("Col9", "Col9"); sqlBulkCopy2.ColumnMappings.Add("Col10", "Col10"); sqlBulkCopy2.ColumnMappings.Add("Position", "Position"); sqlBulkCopy2.WriteToServer(entitySubTable); } string sql = @"DECLARE @inserted0 TABLE ([Id] int, [Position] [int]); MERGE into TestAddSortByBulkCopy t USING #EntityTemp AS s ON 1=0 WHEN NOT MATCHED THEN INSERT ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10], [No]) VALUES (s.[Col1], s.[Col2], s.[Col3], s.[Col4], s.[Col5], s.[Col6], s.[Col7], s.[Col8], s.[Col9], s.[Col10], s.[No]) OUTPUT INSERTED.[Id], s.Position INTO @inserted0; insert into TestAddSortByBulkCopySub ([Id2], [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10]) select i.id AS id2, s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6, s.Col7, s.Col8, s.Col9, s.Col10 from #EntitySubTemp s inner join @inserted0 i on s.[Position] = i.[Position] "; r = cn.Execute(sql); } sw.Stop(); Console.WriteLine($"通过 BulkCopy 导入数据{r}行 毫时{sw.ElapsedMilliseconds}"); }
执行结果总结
两篇文章进行总结，dapper采用insert into table() values () 方式一行行加数据，但性能上还是挺不错的；efcore当数据大于两行则采用Merge方式，性能上略低于dapper，级联上性能比较差了，最严重的问题是批量插入顺序不对；freesql采用insert into table() values (), (), ()一次性增加多行，单表查询性能是最差的，估计是代码上问题而不是sql语句问题；Bulkcopy的性能是最好的，毕竟他是ADO.net针对大量数据而设计的。
