1. Dotnet9首页
  2. .NET
  3. .NET相关

EFCore批量操作内幕

背景

EntityFramework Core有许多新的特性,其中一个重要特性是 批量操作。

批量操作意味着不需要为每次Insert/Update/Delete操作发送单独的命令,而是在一次SQL请求中发送批量组合指令。

EFCore批量操作实践

批处理是期待已久的功能,社区多次提出要求。现在EFCore支持开箱即用确实很棒,可以提高应用程序的性能和速度。

P1 对比实践

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

 另一种观察EFCore生成sql的方法:   

添加Nlog支持,关注Microsoft.EntityFrameworkCore.Database.Command 日志

<logger name=”Microsoft.EntityFrameworkCore.Database.Command” minlevel=”Debug” writeTo=”sql” />

定义插入模型Category, 插入4个实体,这里为什么强调4,请留意下文。

public class Category
    {
        public int Id { get; set; }
        public int CategoryID { get; set; }
        public string CategoryName { get; set; }
    }

/*EFCore 查看模型属性,有Id使用id作为主键,
没有Id,搜索public "{TableName}Id"作为主键,默认为int形主键设置标记列自增;
*/

info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Categories] (
          [Id] int NOT NULL IDENTITY,
          [CategoryID] int NOT NULL,
          [CategoryName] nvarchar(max) NULL,
          CONSTRAINT [PK_Categories] PRIMARY KEY ([Id])
      );

using (var db = new BloggingContext())
{
  db.Categories.Add(new Category() { CategoryID = 1, CategoryName = "Clothing" });
  db.Categories.Add(new Category() { CategoryID = 2, CategoryName = "Footwear" });
  db.Categories.Add(new Category() { CategoryID = 3, CategoryName = "Accessories" });
  db.Categories.Add(new Category() { CategoryID = 4, CategoryName = "Accessories" });
  db.SaveChanges();
} 

当执行SaveChanges(), 日志显示:

info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@p0='1', @p1='Clothing' (Size = 4000), @p2='2', @p3='Footwear' (Size = 4000), @p4='3', @p5='Accessories' (Size = 4000), @p6='4', @p7='Accessories' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
      MERGE [Categories] USING (
      VALUES (@p0, @p1, 0),
      (@p2, @p3, 1),
      (@p4, @p5, 2),
      (@p6, @p7, 3)) AS i ([CategoryID], [CategoryName], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([CategoryID], [CategoryName])
      VALUES (i.[CategoryID], i.[CategoryName])
      OUTPUT INSERTED.[Id], i._Position
      INTO @inserted0;

      SELECT [t].[Id] FROM [Categories] t
      INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
      ORDER BY [i].[_Position];

从SQL Profiler追溯到的SQL:

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Categories] USING (
VALUES (@p0, @p1, 0),
(@p2, @p3, 1),
(@p4, @p5, 2),
(@p6, @p7, 3)) AS i ([CategoryID], [CategoryName], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([CategoryID], [CategoryName])
VALUES (i.[CategoryID], i.[CategoryName])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [Categories] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];

',N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 nvarchar(4000),@p4 int,@p5 nvarchar(4000),@p6 int,@p7 nvarchar(4000)',@p0=1,@p1=N'Clothing',@p2=2,@p3=N'Footwear',@p4=3,@p5=N'Accessories',@p6=4,@p7=N'Accessories'

如你所见,批量插入没有产生4个独立的语句,而是被组合为一个传参存储过程脚本(用列值作为参数);

如果使用EF6执行相同的代码,则在SQL Server Profiler中将看到4个独立的插入语句 。

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

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

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

 P2  sp_executesql ?

起关键作用的 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个参数

P3 豁然开朗

SqlServer  sp_executesql存储过程最多支持2100个批量操作形成的列值参数,所以遇到很大数量的批量操作,EFCore SqlProvider会帮我们将批量操作分块传输, 

实际上EFCore 对于少于4个的批量命令,不会使用sp_executesql 存储过程,我这边自己根据官方验证确实如此:

https://github.com/aspnet/EntityFrameworkCore/pull/10091

https://github.com/aspnet/EntityFrameworkCore/blob/v3.1.0-preview3.19554.8/src/EFCore.Relational/Update/Internal/CommandBatchPreparer.cs

估摸着EFCore使用sp_executesql 也是有点耗资源的,对于小批量(小于4条的批量操作)依旧是产生单条sql。

// 同时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实现过程跟背后的存储载体密切相关)

    –  对于小批量操作(当前EFCore默认MinBatchSize为4》),EFCore不会启用sp_executesql

 - 大批量操作会使用存储过程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());

https://github.com/aspnet/EntityFrameworkCore/issues/6604

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters?redirectedfrom=MSDN    

EFCore批量操作内幕

原文出处:博客园【Dotnet Plus】

原文链接:https://www.cnblogs.com/JulianHuang/p/11897788.html

本文观点不代表Dotnet9立场,转载请联系原作者。

发表评论

登录后才能评论

联系我们

186-2806-45xx

在线咨询:点击这里给我发消息

邮件:632871194@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code