使用 C# 和 EF Core 大容量SQL插入测试
无论您是在构建数据分析平台、迁移旧系统，还是吸引大量新用户，都可能需要将大量数据插入数据库。
一个接一个地插入唱片感觉就像在慢动作中看着油漆干涸。传统方法无法解决问题。
因此，了解 C# 和 EF Core 的快速批量插入技术变得至关重要。
在今天的期刊中，我们将探讨在 C# 中执行批量插入的几个选项：
Dapper EF Core EF Core Bulk Extensions SQL Bulk Copy
这些示例基于 SQL Server 中具有相应表的类。UserUsers
public class User
{
public int Id { get; set; }
public string Email { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string PhoneNumber { get; set; }
}
这不是批量插入实现的完整列表。有一些选项我没有探索，比如手动生成 SQL 语句和使用表值参数。
EF Core 简单方法
让我们从使用 EF Core 的简单示例开始。我们正在创建一个实例，添加一个对象，并调用 .这会将每条记录逐个插入数据库。换言之，每条记录都需要往返数据库一次。ApplicationDbContextUserSaveChangesAsync
using var context = new ApplicationDbContext();
foreach (var user in GetUsers())
{
context.Users.Add(user);
await context.SaveChangesAsync();
}
结果和你预期的一样差：
EF Core - Add one and save, for 100 users: 20 ms
EF Core - Add one and save, for 1,000 users: 260 ms
EF Core - Add one and save, for 10,000 users: 8,860 ms
我省略了结果和记录，因为它们执行时间太长。100,0001,000,000
我们将以此作为“如何不进行批量插入”的示例。
Dapper 简单插入
Dapper 是用于 .NET 的简单 SQL 到对象映射器。它允许我们轻松地将对象集合插入到数据库中。
我正在使用 Dapper 的功能将集合解包为 SQL 语句。INSERT
using var connection = new SqlConnection(connectionString);
connection.Open();
const string sql =
@"
INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
VALUES (@Email, @FirstName, @LastName, @PhoneNumber);
";
await connection.ExecuteAsync(sql, GetUsers());
结果比最初的例子要好得多：
Dapper - Insert range, for 100 users: 10 ms
Dapper - Insert range, for 1,000 users: 113 ms
Dapper - Insert range, for 10,000 users: 1,028 ms
Dapper - Insert range, for 100,000 users: 10,916 ms
Dapper - Insert range, for 1,000,000 users: 109,065 ms
EF Core 添加和保存
但是，EF Core 仍然没有认输。第一个例子是故意实施得很差。EF Core 可以将多个 SQL 语句批处理在一起，因此让我们使用它。
如果我们进行简单的更改，我们可以获得显着更好的性能。首先，我们将所有对象添加到 .然后，我们将只调用一次。ApplicationDbContextSaveChangesAsync
EF 将创建一个批处理的 SQL 语句（将许多语句组合在一起），并将它们一起发送到数据库。这减少了往返数据库的次数，从而提高了性能。INSERT
using var context = new ApplicationDbContext();
foreach (var user in GetUsers())
{
context.Users.Add(user);
}
await context.SaveChangesAsync();
以下是此实现的基准测试结果：
EF Core - Add all and save, for 100 users: 2 ms
EF Core - Add all and save, for 1,000 users: 18 ms
EF Core - Add all and save, for 10,000 users: 203 ms
EF Core - Add all and save, for 100,000 users: 2,129 ms
EF Core - Add all and save, for 1,000,000 users: 21,557 ms
请记住，Dapper 需要 109 秒才能插入记录。我们可以在 ~21 秒内使用 EF Core 批处理查询实现相同的目的。1,000,000
EF Core AddRange 和保存
这是上一示例的替代方法。我们可以调用并传入集合，而不是调用所有对象。AddAddRange
我想展示这个实现，因为我更喜欢它而不是前一个。
using var context = new ApplicationDbContext();
context.Users.AddRange(GetUsers());
await context.SaveChangesAsync();
结果与前面的示例非常相似：
EF Core - Add range and save, for 100 users: 2 ms
EF Core - Add range and save, for 1,000 users: 18 ms
EF Core - Add range and save, for 10,000 users: 204 ms
EF Core - Add range and save, for 100,000 users: 2,111 ms
EF Core - Add range and save, for 1,000,000 users: 21,605 ms
EF Core 批量扩展
有一个很棒的库，称为 EF Core 批量扩展，我们可以使用它来提高性能。使用此库，您可以做的不仅仅是批量插入，因此我建议您探索它。该库是开源的，如果您符合免费使用标准，则具有社区许可证。有关更多详细信息，请查看许可部分。
对于我们的用例，该方法是一个很好的选择。我们可以传递对象的集合，它将执行 SQL 批量插入。BulkInsertAsync
using var context = new ApplicationDbContext();
await context.BulkInsertAsync(GetUsers());
性能同样令人惊叹：
EF Core - Bulk Extensions, for 100 users: 1.9 ms
EF Core - Bulk Extensions, for 1,000 users: 8 ms
EF Core - Bulk Extensions, for 10,000 users: 76 ms
EF Core - Bulk Extensions, for 100,000 users: 742 ms
EF Core - Bulk Extensions, for 1,000,000 users: 8,333 ms
为了进行比较，我们需要 ~21 秒来插入包含 EF Core 批处理查询的记录。我们可以在短短 8 秒内对批量扩展库执行相同的操作。1,000,000
SQL 大容量复制
最后，如果无法从 EF Core 获得所需的性能，可以尝试使用 .SQL Server 本机支持大容量复制操作，因此让我们使用它。SqlBulkCopy
此实现比 EF Core 示例稍微复杂一些。我们需要配置实例并创建一个包含要插入的对象的实例。SqlBulkCopyDataTable
using var bulkCopy = new SqlBulkCopy(ConnectionString);
bulkCopy.DestinationTableName = "dbo.Users";
bulkCopy.ColumnMappings.Add(nameof(User.Email), "Email");
bulkCopy.ColumnMappings.Add(nameof(User.FirstName), "FirstName");
bulkCopy.ColumnMappings.Add(nameof(User.LastName), "LastName");
bulkCopy.ColumnMappings.Add(nameof(User.PhoneNumber), "PhoneNumber");
await bulkCopy.WriteToServerAsync(GetUsersDataTable());
然而，性能非常快：
SQL Bulk Copy, for 100 users: 1.7 ms
SQL Bulk Copy, for 1,000 users: 7 ms
SQL Bulk Copy, for 10,000 users: 68 ms
SQL Bulk Copy, for 100,000 users: 646 ms
SQL Bulk Copy, for 1,000,000 users: 7,339 ms
下面介绍如何创建一个对象列表并填充它：DataTable
DataTable GetUsersDataTable()
{
var dataTable = new DataTable();
dataTable.Columns.Add(nameof(User.Email), typeof(string));
dataTable.Columns.Add(nameof(User.FirstName), typeof(string));
dataTable.Columns.Add(nameof(User.LastName), typeof(string));
dataTable.Columns.Add(nameof(User.PhoneNumber), typeof(string));
foreach (var user in GetUsers())
{
dataTable.Rows.Add(
user.Email, user.FirstName, user.LastName, user.PhoneNumber);
}
return dataTable;
}
结果
以下是所有批量插入实现的结果：
| Method | Size | Speed
|------------------- |----------- |----------------:
| EF_OneByOne | 100 | 19.800 ms |
| EF_OneByOne | 1000 | 259.870 ms |
| EF_OneByOne | 10000 | 8,860.790 ms |
| EF_OneByOne | 100000 | N/A |
| EF_OneByOne | 1000000 | N/A |
| Dapper_Insert | 100 | 10.650 ms |
| Dapper_Insert | 1000 | 113.137 ms |
| Dapper_Insert | 10000 | 1,027.979 ms |
| Dapper_Insert | 100000 | 10,916.628 ms |
| Dapper_Insert | 1000000 | 109,064.815 ms |
| EF_AddAll | 100 | 2.064 ms |
| EF_AddAll | 1000 | 17.906 ms |
| EF_AddAll | 10000 | 202.975 ms |
| EF_AddAll | 100000 | 2,129.370 ms |
| EF_AddAll | 1000000 | 21,557.136 ms |
| EF_AddRange | 100 | 2.035 ms |
| EF_AddRange | 1000 | 17.857 ms |
| EF_AddRange | 10000 | 204.029 ms |
| EF_AddRange | 100000 | 2,111.106 ms |
| EF_AddRange | 1000000 | 21,605.668 ms |
| BulkExtensions | 100 | 1.922 ms |
| BulkExtensions | 1000 | 7.943 ms |
| BulkExtensions | 10000 | 76.406 ms |
| BulkExtensions | 100000 | 742.325 ms |
| BulkExtensions | 1000000 | 8,333.950 ms |
| BulkCopy | 100 | 1.721 ms |
| BulkCopy | 1000 | 7.380 ms |
| BulkCopy | 10000 | 68.364 ms |
| BulkCopy | 100000 | 646.219 ms |
| BulkCopy | 1000000 | 7,339.298 ms |
带走
SqlBulkCopy握住表冠以获得最大原始速度。但是，EF Core 批量扩展提供了出色的性能，同时保持了 Entity Framework Core 众所周知的易用性。
最佳选择取决于您项目的具体需求：
- 性能才是最重要的？ 是您的解决方案。SqlBulkCopy
- 需要出色的速度和简化的开发吗？EF Core 是一个明智的选择。