Net 优化 Dapper 批量操作性能

1.后端批量插入代码

            using (var connection = new System.Data.SqlClient.SqlConnection("data source=XXX;initial catalog=XXX;persist security info=True;user id=XXX;password=XXX;"))
            {
                connection.Open();
                var sql = "INSERT INTO XXX VALUES(@Code, @AddTime, @AddUser, @ControllerName, @ActionName, @Remark)";
                var list = new List<XXX>();
                Enumerable.Range(0, 10).ToList().ForEach(x =>
                {
                    list.Add(new XXX
                    {
                        Code = DateTime.Now.ToString($"{x}-yyyy-MM-dd HH:mm:ss.ffff"),
                        AddTime = DateTime.Parse("2021-01-01"),
                        AddUser = $"AddUser-{x}",
                        ControllerName = $"ControllerName-{x}",
                        ActionName = $"ActionName-{x}",
                        Remark = $"Remark-{x}",
                    });
                });
                var result = connection.Execute(sql, list);
            }
        }

2.通过Microsoft SQL Server Manager Studio的Sql Server ProFiler【Tools/Sql Server ProFiler】可以看到多条插入记录，所以数据量大了通过【步骤1】批量添加速度达不到预期

 

3.可以在后端把多条数据拼成一条脚本一次性插入

INSERT INTO XXX(Code, AddTime, AddUser, ControllerName, ActionName, Remark)
VALUES(@Code0, @AddTime0, @AddUser0, @ControllerName0, @ActionName0, @Remark0)
    ,(@Code1, @AddTime1, @AddUser1, @ControllerName1, @ActionName1, @Remark1)
    ,(@Code2, @AddTime2, @AddUser2, @ControllerName2, @ActionName2, @Remark2)