background

EntityFramework Core has many new features, one of which is the bulk operation.

Bulk operations mean that you don’t need to send a separate command for each Insert/Update/Delete operation, but instead send a batch combination instruction in one SQL request.

EFCore batch operation practice

Batch processing is a long-awaited feature, and the community has repeatedly asked for it. Now EFCore support is great out of the box, which can improve the performance and speed of your application.

P1 comparison practice

Let’s take a common bulk insert as an example. Use SQL Server Profiler to observe the SQL statements actually generated and executed.

There is also a way to focus on the SQL statements generated by EFCore, add Nlog support, and focus on the Microsoft.EntityFrameworkCore.Database.Command log.

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

 

 

// Three records have been added to the category table and saved 
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();
}

 

 

When executing SaveChanges(), the SQL traced from the SQL Profiler:

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 '

As you can see, bulk inserts do not produce 3 separate statements, but are combined into a pass-through stored procedure script (using column values ​​as arguments); if you use EF6 to execute the same code, you will see it in SQL Server Profiler. Go to 3 separate insert statements. Below is a comparison screenshot of EFCore and EF6 bulk inserts:

EFCore_batch_operation_2.png
EFCore_batch_operation_3.png

1 In terms of performance and speed, EFCore batch insertion has an advantage.
2 If the database is for cloud deployment, EF6 will run these queries and will incur additional traffic costs.

After verification: EFCore batch update, batch delete function, EFCore issued SQL scripts built using sp_executesql stored procedure + batch parameters.

 P2 in-depth analysis

The sp_executesql stored procedure that plays a key role: a statement or batch that can be executed multiple times (with parameters)

 

 

-- 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 ] }  
]  

 

 

Note the official restrictions:

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 stored procedure can use up to 2100 parameters

P3 suddenly becomes cheerful

The SqlServer sp_executesql stored procedure supports up to 2100 batch operations to form column value parameters, so when encountering a large number of batch operations, EFCore SqlProvider will help us to bulk transfer the batch operations.

This is why we see block transmission when we actually use it in large quantities.

At the same time, EFCore has opened [Configure relational database batch operation size]:

Protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
{
    String sConnString = @"Server = localhost; Database = EFSampleDB;Trusted_Connection = true;";
    optionbuilder.UseSqlServer(sConnString , b => b.MaxBatchSize( 1 ));     //The number of SQL statements for batch operations can also be set to 1 to disable bulk inserts.
}

Summary

1 EFCore supports batch operations compared to EF6, which can effectively improve application performance.

2 EFCore’s batch operation capability is supported by the corresponding DataBaseProvider (the Provider implementation process is closely related to the storage carrier behind it)

For SQL to pay attention to this stored procedure sp_executesql, the official plain text shows that the column value parameter of the batch operation is up to 2100. This key factor determines that it will be transferred in chunks during high-volume operations.

3 Another method of batch operation, here also click: Construct Rawsql [EFCore supports Rawsql].

Sqlite does not support stored procedures, and this can be used to improve performance for batch insertion.

 

 

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()));
the 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

Orignal link:https://www.cnblogs.com/JulianHuang/p/11897788.html