Dapper, Ef core, Freesql 插入大量数据性能比较（一）
需求：导入9999行数据时Dapper, Ef core, Freesql 谁的性能更优，是如何执行的，级联增加谁性能更佳。
确认方法：sql server 的 sys.dm_exec_query_stats
SELECT TOP 1000 (select [text] from sys.dm_exec_sql_text(QS.sql_handle)) as '数据库语句', QS.execution_count AS '执行次数', QS.total_elapsed_time AS '耗时', QS.total_logical_reads AS '逻辑读取次数', QS.total_logical_writes AS '逻辑写入次数', QS.total_physical_reads AS '物理读取次数', QS.creation_time AS '执行时间', * FROM sys.dm_exec_query_stats QS WHERE QS.creation_time > '2021-04-11 09:42:30'
准备：创建表
CREATE TABLE [dbo].[TestAddSortByXXXX]( [Id] [int] IDENTITY(1,1) NOT NULL, [No] [int] NULL, [Col1] [nvarchar](50) NULL, [Col2] [nvarchar](50) NULL, [Col3] [nvarchar](50) NULL, [Col4] [nvarchar](50) NULL, [Col5] [nvarchar](50) NULL, [Col6] [nvarchar](50) NULL, [Col7] [nvarchar](50) NULL, [Col8] [nvarchar](50) NULL, [Col9] [nvarchar](50) NULL, [Col10] [nvarchar](50) NULL, CONSTRAINT [PK_TestAddSortByXXXX] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[TestAddSortByXXXXSub]( [Id] [int] IDENTITY(1,1) NOT NULL, [Id2] [int] NULL, [Col1] [nvarchar](50) NULL, [Col2] [nvarchar](50) NULL, [Col3] [nvarchar](50) NULL, [Col4] [nvarchar](50) NULL, [Col5] [nvarchar](50) NULL, [Col6] [nvarchar](50) NULL, [Col7] [nvarchar](50) NULL, [Col8] [nvarchar](50) NULL, [Col9] [nvarchar](50) NULL, [Col10] [nvarchar](50) NULL, CONSTRAINT [PK_TestAddSortByXXXXSub] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
构建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"), }; datas.Add(item);
}
Dapper：
static void AddDataByDapper(List<Entity> datas) { int r = 0; Stopwatch sw = new Stopwatch(); sw.Start(); using (var conn = new SqlConnection(connString)) { conn.Open(); string sql = "insert into TestAddSortByDapper([No], Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10) values(@No, @Col1, @Col2, @Col3, @Col4, @Col5, @Col6, @Col7, @Col8, @Col9, @Col10);"; r = conn.Execute(sql, datas); } sw.Stop(); Console.WriteLine($"通过 Dapper 导入数据{r}行 毫时{sw.ElapsedMilliseconds}"); }
执行结果总结
-- 数据库实际执行数据
(@Col1 nvarchar(4000),@Col10 nvarchar(4000),...) insert into TestAddSortByDapper([No], Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10) values(@No, @Col1, @Col2, @Col3, @Col4, @Col5, @Col6, @Col7, @Col8, @Col9, @Col10);
从结果我们可以看到，dapper使用的是 insert into table () values () 方式循环执行9999次，代码总耗时3-4秒。
EfCore：
static void AddDataByEfCore(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}"); } [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; } }
执行结果总结
(@p0 nvarchar(4000),@p1 nvarchar(4000),...,@p460 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];
从结果我们可以看到，EfCore使用的是 Merge 方式增加数据，但数据库变量最多定义462个，所以每次只能增加42行数据，执行了238+3次，但最大的疑问是执行了两次，而且插入表数据顺序错了（估计是EfCore代码上使用了Parallel.For方法，有懂的朋友能否解答一下），代码总耗时4-5秒。
Freesql：
static void AddDataByFreeSql(List<Entity> datas) { int r1 = 0; Stopwatch sw = new Stopwatch(); sw.Start(); IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.SqlServer, connString) .UseAutoSyncStructure(false) .Build(); fsql.Insert<Entity>(datas).ExecuteSqlBulkCopy(); sw.Stop(); Console.WriteLine($"通过 Freesql 毫时{sw.ElapsedMilliseconds}"); } [FreeSql.DataAnnotations.Table(Name = "TestAddSortByFreesql", DisableSyncStructure = true)] public class Entity { [FreeSql.DataAnnotations.Column(Name = "id", IsPrimary = true, IsIdentity = true)] 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; } }
执行结果总结
create procedure sys.sp_tablecollations_100 (@object nvarchar(4000)) as select colid = s_tcv.colid, name = s_tcv.name, tds_collation = s_tcv.tds_collation_100, "collation" = s_tcv.collation_100 from sys.spt_tablecollations_view s_tcv where s_tcv.object_id = object_id(@object, 'local') order by colid select @@trancount; SET FMTONLY ON select * from [TestAddSortByFreesql] SET FMTONLY OFF exec ..sp_tablecollations_100 N'.[TestAddSortByFreesql]'
从结果我们可以看到，上面sql语句并不是实际保存数据语句，实际写入数据库的应该是SqlBulkCopy。
从目前结果来看，单表增加大量数据，时间上 Freesql > Dapper > EfCore。
ADO.NET SqlBulkCopy 复制（最优方案）
static void AddDataByBulkCopy(List<Entity> datas) { Stopwatch sw = new Stopwatch(); var dt = new DataTable(); dt.Columns.Add("No", typeof(int)); dt.Columns.Add("Col1", typeof(string)); dt.Columns.Add("Col2", typeof(string)); dt.Columns.Add("Col3", typeof(string)); dt.Columns.Add("Col4", typeof(string)); dt.Columns.Add("Col5", typeof(string)); dt.Columns.Add("Col6", typeof(string)); dt.Columns.Add("Col7", typeof(string)); dt.Columns.Add("Col8", typeof(string)); dt.Columns.Add("Col9", typeof(string)); dt.Columns.Add("Col10", typeof(string)); foreach (var item in datas) { var dr = dt.NewRow(); dr["No"] = item.No; dr["Col1"] = item.Col1; dr["Col2"] = item.Col2; dr["Col3"] = item.Col3; dr["Col4"] = item.Col4; dr["Col5"] = item.Col5; dr["Col6"] = item.Col6; dr["Col7"] = item.Col7; dr["Col8"] = item.Col8; dr["Col9"] = item.Col9; dr["Col10"] = item.Col10; dt.Rows.Add(dr); } sw.Start(); using (SqlConnection cn = new SqlConnection(connString)) { cn.Open(); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(cn)) { sqlBulkCopy.BatchSize = dt.Rows.Count; sqlBulkCopy.BulkCopyTimeout = 1800; sqlBulkCopy.DestinationTableName = "TestAddSortByBulkCopy"; 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.WriteToServer(dt); } } sw.Stop(); Console.WriteLine($"通过 BulkCopy 毫时{sw.ElapsedMilliseconds}"); }
执行结果总结
并没有在 sys.dm_exec_query_stats 上产生结果，但他的性能是最佳的。
下一篇，来看看级联操作上谁能更胜一筹。
