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)