使用EF进行高性能插入和复制控制

本文关键字：复制 控制 插入 高性能 EF 使用 | 更新日期: 2023-09-27 18:00:20

我了解SQL的基本知识，因此我学会了创建如下插入查询：

queryAccount.AppendLine(
    string.Format(
        "Insert INTO Account(Number_Account, DoID, ClientID) Select "
            + "{0}, "
            + "(Select id From AccountDO Where Number_do = {1}), "
            + "(Select id From Client Where Number_Client = {2})"
            + "Where not exists(Select * From Account Where Number_Account = {0});",
        item.Client.NumberAccount,
        item.Client.NumberDo,
        item.Client.NumberClient));

在该查询中，我将数据添加到具有两个FK（DoID和ClientID）的表"Account"中，并检查该帐户是否已经存在。通常，为了插入平面文件中的数据，我使用字符串生成器来创建多个插入查询。

这在一些要求较低的项目中效果良好，但现在我面临着更大的挑战。我需要创建一个每天导入新数据的网站，因此让"导入模块"遵循最佳实践是很重要的。

到目前为止我做了什么：

  • 我的数据库模型：大约20个具有各种关系的表（包括多对多）
  • 使用实体模型代码生成器在APS中生成相应的模型。Net项目

我需要做什么：

  • 执行大容量数据插入，我的方法很慢，可能不安全
  • 插入避免重复的数据，并使用外部表的正确ID

这就是为什么我需要你的帮助，我如何才能最好地利用现有的技术来实现我的目标？是否可以使用实体框架（EF）来毫不费力地将帐户列表添加到数据集中？。

使用EF进行高性能插入和复制控制

您的需求：

  1. 执行批量数据插入，快速且安全
  2. 插入数据，避免与外部表的正确ID重复
  3. 充分利用现有技术
  4. 使用实体框架（EF）毫不费力地将帐户列表添加到数据集中

如果您使用EF插入C#代码中的数据，可以考虑使用参数化sql查询，以使插入更安全地免受sql注入攻击。

使用Data.SqlClient.SqlCommand.Parameters.Add:

MSDN：SqlCommand。参数属性

public void InsertCustomer(Integer customerID, DateTime activityDate) {
    String sql = "INSERT INTO Customers (customerID, ActivityDate) VALUES (@customerID, @activityDate);";
    Data.SqlClient.SqlCommand cmd = new Data.SqlClient.SqlCommand(sql);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add("@customerID ", Data.SqlDbType.Int).Value = customerID;
    cmd.Parameters.Add("@activityDate ", Data.SqlDbType.DateTime).Value = activityDate;
    try {
        using (SqlConnection connection = new Data.SqlClient.SqlConnection(YourConnectionString)) {
            connection.Open();
            cmd.Connection = connection;
            cmd.ExecuteNonQuery();
        }
    } catch (Exception ex) {
        throw ex;
    }
}

但是，如果使用SSIS或T-SQL BULK INSERTS，则插入作业应该运行得更快。

以下是我找到的资源：

使用SSIS ETL包插入和更新记录

  • SQL Server Integration Services（SSIS）
  • 提取转换负载（ETL）

批量导入和导出数据（SQL Server）

  • （cmd行）bcp实用程序
  • （T-SQL）大容量插入
  • （T-SQL）插入。。。从OPENROWSET中选择*（批量…）

优化批量导入性能

  • 使用最少日志记录
  • 将数据从多个客户端并行导入到单个表
  • 使用批次
  • 禁用触发器
  • 禁用约束
  • 对数据文件中的数据进行排序
  • 控制锁定行为
  • 以本机格式导入数据

批量导入中最小日志记录的先决条件

最少日志记录要求目标表满足以下条件：

  • 表未被复制
  • 指定了表锁定（使用TABLOCK）
  • 表不是内存优化的表

表是否可以进行最少的日志记录还取决于表是否被索引，如果是，表是否为空：

  • 如果表没有索引，则数据页的日志记录量最低
  • 如果表没有聚集索引，但有一个或多个非聚集索引，则数据页的日志记录总是最低限度的。然而，如何记录索引页取决于表是否为空：
    • 如果表为空，则索引页的日志记录量最低
    • 若表不为空，则索引页将被完全记录
    • 如果表具有聚集索引并且为空，则数据页和索引页的日志记录量都最低。相反，如果表具有聚集索引并且不为空，则无论恢复模型如何，数据页和索引页都会被完全记录

SQL Server 中通过TSQL进行大容量插入

  • 批量插入-SQL Server 2005；2008
  • INSERT…SELECT*FROM OPENROWSET（BULK…）-SQL Server 2005&2008

假设您已经正确地映射了数据库（代码优先或数据库优先），那么应该有一些表映射到您的上下文。例如：

public class DataModel : DbContext
{
    /* more code ... */
    public virtual DbSet<User> Users { get; set; }
    /* more code ... */
}

DbSet类公开了一个可以用于大容量插入的AddRange方法。因此，假设您有一个User对象的集合，您可以这样做：

public class SomeClass
{
    public int InsertUsers(params User[] users)
    {
        using(var context = new DataModel())
        {
            context.Users.AddRange(users);
        }
    }
}

用户将被插入一个事务中（假设底层数据存储支持事务）。

相关文章：