将数百万行插入SQL Server数据库的最佳方法

本文关键字：数据库 最佳 方法 Server SQL 数百万 插入 | 更新日期: 2023-09-27 17:59:07

我正在从多个提要收集数据，包括api、excel文件、文本文件和word文件。我使用关系数据库来存储所有关系。最多有10种一对多或多对多关系。

我使用的方法是将每个条目写入.csv文件，然后调用存储过程来大容量插入所有条目。因此，在这种情况下，我可以为数据库中的每个表提供10个单独的文件。

我遇到了两个问题：

  • 将文件传输到数据库服务器（同一网络）
  • 主键，我需要使用guid而不是自动递增

最佳性能方法是什么？

将数百万行插入SQL Server数据库的最佳方法

2个单词：BULK INSERT

如果您已经有一个csv文件，这只是编写一些SQL或C#（无论您喜欢哪种）来执行大容量插入的情况。

以下是SQL文档：https://msdn.microsoft.com/en-gb/library/ms188365.aspx

BULK INSERT MySchema.MyTable
FROM 'c:'myfile.csv'
WITH 
  (
     FIELDTERMINATOR =',',
     ROWTERMINATOR =''n'
  );

以及C#文档：https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.110%29.aspx

我为此构建了一个小工具https://github.com/MikaelEliasson/EntityFramework.Utilities#batch-插入实体或者Nuget链接https://www.nuget.org/packages/EFUtilities/

它将使用内存列表中的SqlBulkCopy。它使用EF元数据，所以您不必自己配置。代码如下：

using (var ctx = new Context())
{
    EFBatchOperation.For(ctx, ctx.Locations).InsertAll(locations);
}

这是我做的一个小演示https://github.com/MikaelEliasson/EFUtilitiesDemos/blob/master/BulkInsertAndUpdate/Program.cs#L46

速度在很大程度上取决于实体的比特数。我的测试表明，我可以为中等大小的实体插入大约100000个对象/秒。

如果您有guid，那么关系插入应该很容易完成，就像您已经完成的那样。

因为您有多个插入，所以我建议您使用事务范围。看见https://github.com/MikaelEliasson/EntityFramework.Utilities/issues/26

编辑

如果您更喜欢使用int或longs，它将包含在下一个版本中。这将需要更长的时间，但您可以为存储生成的Id启用Id返回。

请参阅：https://github.com/MikaelEliasson/EntityFramework.Utilities/blob/release20/EntityFramework.Utilities/Tests/InsertTests.cs#L125

该代码目前正在运行，但尚未准备好发布。但如果你现在想尝试的话，你可以自己下载并构建realease20分支。

相关文章：