1

对比实践

以常见的批量插入为例,使用SQL Server Profiler观察产生并执行的SQL语句。

// category表添加3条记录并执行保存
using (var c= new SampleDBContext())
{
    c.Categories.Add(new Category() { CategoryID = 1, CategoryName = "Clothing" });
    c.Categories.Add(new Category() { CategoryID = 2, CategoryName = "Footwear" });
    c.Categories.Add(new Category() { CategoryID = 3, CategoryName = "Accessories" });
    c.SaveChanges();
}

当执行SaveChanges(), 从SQL Profiler追溯到的SQL

exec sp_executesql N'SET NOCOUNT ON;INSERT INTO [Categories] ([CategoryID], [CategoryName])
VALUES (@p0, @p1),(@p2, @p3),(@p4, @p5);',N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 nvarchar(4000),@p4 int,@p5 nvarchar(4000)',
@p0=1,@p1=N'Clothing',@p2=2,@p3=N'Footwear',@p4=3,@p5=N'Accessories'

如你所见,批量插入没有产生3个独立的语句,而是被组合为一个传参存储过程脚本(用列值作为参数);如果使用EF6执行相同的代码,则在SQL Server Profiler中将看到3个独立的插入语句 。下面EFCore、EF6批量插入的对比截图

EFCore批量操作,你真的清楚吗_java

EFCore批量操作,你真的清楚吗_java_02

① 就性能和速度而言,EFCore批量插入更具优势

② 若数据库是针对云部署,EF6运行这些查询,还将产生额外的流量成本

经过验证:EFCore批量更新、批量删除功能,EFCore均发出了使用sp_executesql存储过程+批量参数构建的SQL脚本。

2

深入分析

起关键作用的存储过程sp_executesql:可以多次执行的语句或批处理 (可带参)

- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

注意官方限制

The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server procedures can have, at most, 2100 parameters. Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.       // SQL存储过程最多可使用2100个参数

3

豁然开朗

SqlServer sp_executesql存储过程最多支持2100个批量操作形成的列值参数,所以遇到很大数量的批量操作,EFCore SqlProvider会帮我们将批量操作分块传输,这也是我们在实际大批量使用时看到分块发送的原因。

EFCore开放了【配置关系型数据库批量操作大小】

protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
{
    string sConnString = @"Server=localhost;Database=EFSampleDB;Trusted_Connection=true;";
    optionbuilder.UseSqlServer(sConnString , b => b.MaxBatchSize(1)); 
   // 批量操作的SQL语句数量,也可设定为1禁用批量插入
}

总结


① EFCore 相比EF6,已经支持批量操作,能有效提高应用程序的性能

② EFCore的批量操作能力,由对应的DataBaseProvider支撑(Provider实现过程跟背后的存储载体密切相关);关注SQL存储过程sp_executesql,官方明文显示批量操作的列值参数最多2100个,这个关键因素决定了在大批量操作的时候 依旧会被分块传输。

③ 另外一个批量操作的方法,这里也点一下:构造Rawsql 【EFCore也支持Rawsql】

  sqlite不支持存储过程,为批量插入提高性能,可采用此方案


var insertStr = new StringBuilder();
insertStr.AppendLine("insert into ProfileUsageCounters (profileid,datetime,quota,usage,natureusage) values");
var txt = insertStr.AppendLine(string.Join(',', usgaeEntities.ToList().Select(x =>
{
       return $"({x.ProfileId},{x.DateTime},{x.Quota},{x.Usage},{x.NatureUsage})";
}).ToArray()));
await _context.Database.ExecuteSqlCommandAsync(txt.ToString());