无论您是构建数据分析平台、迁移遗留系统还是引入大量新用户，都可能会需要在数据库中插入大量数据。
一张一张地放入唱片的感觉就像看着油漆慢慢变干一样。传统的方法行不通。
因此，了解使用 C# 和 EF Core 的快速批量插入技术变得至关重要。
在今天的问题中，我们将探讨在 C# 中执行批量插入的几种选项：
- Dapper
- EF 核心
- EF Core 批量扩展
- SQL 批量复制
这些示例基于SQL Server中User具有相应表的类。Users
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 的简单示例开始。我们创建一个ApplicationDbContext实例，添加一个User对象，然后调用SaveChangesAsync。这会将每条记录逐一插入数据库。换句话说，每条记录都需要往返数据库一次。
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,000我省略了带有和记录的结果，1,000,000因为它们执行时间太长。
我们将以此作为“如何不进行批量插入”的示例。
Dapper 简单插入
Dapper是一个简单的 .NET SQL 到对象映射器。它允许我们轻松地将对象集合插入数据库。
我正在使用 Dapper 的功能将集合解开为 SQLINSERT语句。
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 语句批量处理，所以让我们使用它。
如果我们进行简单的更改，就可以获得明显更好的性能。首先，我们将所有对象添加到ApplicationDbContext。然后，我们只调用SaveChangesAsync一次。
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 秒1,000,000内使用 EF Core 批量查询实现相同的效果。
EF Core AddRange 和 Save
这是上一个示例的替代方法。Add我们不必调用所有对象，而是可以调用AddRange并传入一个集合。
我想展示这个实现，因为我更喜欢它而不是以前的一个。
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 Bulk Extensions，我们可以使用它来提高性能。这个库可以做很多事情，而不仅仅是批量插入，所以我建议你去探索一下。这个库是开源的，如果你符合免费使用标准，它有一个社区许可证。查看许可部分了解更多详情。
对于我们的用例来说，该BulkInsertAsync方法是一个很好的选择。我们可以传递对象集合，它将执行 SQL 批量插入
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
|
相比之下，使用 EF Core 批量查询插入记录大约需要21 秒。使用Bulk Extensions库只需8 秒1,000,000即可完成相同操作。
SQL 批量复制
最后，如果我们无法从 EF Core 获得所需的性能，我们可以尝试使用SqlBulkCopy。SQL Server 原生支持批量复制操作，所以让我们使用它。
这个实现比 EF Core 示例稍微复杂一些。我们需要配置实例SqlBulkCopy并创建一个DataTable包含我们要插入的对象。
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 Bulk Extensions提供了出色的性能，同时保持了 Entity Framework Core 以易用性而闻名。
最佳选择取决于您的项目的具体需求：
- 性能才是最重要的？SqlBulkCopy是您的解决方案。
- 需要出色的速度和精简的开发？EF Core 是一个明智的选择。