Dapper, Ef core, Freesql 插入大量資料效能比較（二）

沉默♪☞小傲發表於2021-04-13

在上一篇文章中，我們比較出單表插入9999行資料，Dapper > EfCore > Freesql。在本文中，我們來看看級聯插入

構建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針對大量資料而設計的。

.Net 常用ORM框架對比：EF Core、FreeSql、SqlSuger
2020-12-20
ORM框架SQL
c# 國內外ORM 框架 dapper efcore sqlsugar freesql hisql sqlserver資料常規插入測試效能對比
2022-06-29
C#ORM框架APPSqlSugarServer
Petapoco、Dapper和EF Core的異同
2024-03-07
APP
通過append hint來插入資料，演示它和普通插入資料的效能比較。
2019-01-08
APP
EF Core 二、入門 EF Core
2020-11-20
HashMap，LinkedHashMap，TreeMap讀取大量資料效率的比較
2019-03-15
HashMap
WCF與ASP.NET Core效能比較
2018-04-12
ASP.NET
批量插入 1 萬條資料，DB 查詢構造器和原生 SQL 效能比較
2020-03-17
SQL
深入理解 EF Core：EF Core 寫入資料時發生了什麼？
2020-06-19
深入理解 EF Core：EF Core 讀取資料時發生了什麼？
2020-06-17
EF Core連線PostgreSQL資料庫
2024-08-07
SQL資料庫
【.NET 6】使用EF Core 訪問Oracle+Mysql+PostgreSQL並進行簡單增改操作與效能比較
2022-04-03
OracleMySql
EF Core 三、 EF Core CRUD
2020-11-23
效能比較
2022-06-16
Dapper in .Net Core
2021-12-31
APP
ASP.NET Core使用EF Core操作MySql資料庫
2018-11-30
ASP.NETMySql資料庫
使用Mybatis批量插入大量資料的實踐
2022-01-03
MyBatis
laravel實現100w大量資料插入資料庫
2021-11-11
Laravel資料庫
EF和Dapper之爭的關鍵
2021-09-09
APP
EF Core助力信創國產資料庫
2023-12-20
資料庫
如何使用 EF Core 7 批次刪除資料
2022-12-22
探索JAVA系列(二)LinkedList插入資料真的比ArrayList快嗎？
2019-03-05
Java
Java基礎（二）－普通for迴圈、foreach效能比較
2020-06-01
Java
EF Core 一、重識 EF
2020-11-19
EF Core 原理從原始碼出發(二)
2021-03-14
原始碼
sqlsugar freesql hisql 三個ORM框架效能測試對比
2022-01-07
SqlSugarORM框架
主流資料庫比較
2024-03-29
資料庫
圖資料庫比較
2020-10-11
資料庫
【MyBatis】幾種批量插入效率的比較
2021-09-14
MyBatis
大資料入門課程：Hadoop和spark的效能比較
2018-04-04
大資料HadoopSpark
【EF core】模型視覺化 EF Core Power Tools 【工具】
2024-03-26
模型視覺化
【轉載】【EF Core】Code first 之使用新資料庫
2024-04-11
資料庫
C# 資料操作系列 - 5. EF Core 入門
2020-05-14
C#
[翻譯 EF Core in Action 2.4] 載入相關資料
2019-05-07
python 批量resize效能比較
2019-03-04
Python
HHDESK資料夾比較功能
2023-02-22
difflib： Python 比較資料集
2024-05-09
Python
ASP.NET Core Identity自定義資料庫結構和完全使用Dapper而非EntityFramework Core
2019-08-03
ASP.NETIDE資料庫APPFramework

Dapper, Ef core, Freesql 插入大量資料效能比較（二）

在上一篇文章中，我們比較出單表插入9999行資料，Dapper > EfCore > Freesql。在本文中，我們來看看級聯插入

構建9999行資料

Dapper：

EfCore：

ADO.Net BulkCopy：

相關文章