批量插入性能问题

问：

我有 160k 条记录需要处理，然后存储在 sql server 表中。我从临时表中检索它们，并执行一些验证和检查，以查找可能已存在的缺失字段或记录。

这在实体框架中非常慢，所以我修改了我的代码并希望使用 .我会查看我以前使用过的实体框架扩展，但我的工作不想承担使用它的许可费用。因为我需要对数据进行验证和其他检查，所以我需要将数据表传递给 ，而不是执行 .SqlBulkCopySqlBulkCopyIDataReader

这是我遇到问题的地方，将我的实体转换为具有 160k 行的数据表需要 20 多分钟。有没有人对如何改进其他建议有任何想法，这些建议可以快速使用如此大量的数据进行批量插入？IEnumerable

下面是我用来填充数据读取器的代码。我最初是在反射中完成这一切的，但删除了手动添加行，希望它会更快。

private static DataTable CreateRegisterDatatable(List<ApiRegisterData> apiRegisterData, List<Election> elections, IEnumerable<int> existingElectorIds, IEnumerable<int> missingElectionIds)
{
   var registerTable = new DataTable();

   foreach (var prop in typeof(Register).GetProperties())
   {
       DataColumn column = new DataColumn(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
       registerTable.Columns.Add(column);
   }

   foreach (var apiRegister in apiRegisterData)
   {
       if (!missingElectionIds.Contains(apiRegister.ElectionId) && !existingElectorIds.Contains(apiRegister.ElectorID))
       {
           DataRow row = registerTable.NewRow();
           row["ElectionID"] = elections[0].ElectionID;
           row["ElectorSuffix"] = apiRegister.ElectorSuffix.ToString();
           row["ElectorNumber"] = apiRegister.ElectorNumber;
           row["Title"] = string.IsNullOrEmpty(apiRegister.Title) ? DBNull.Value : apiRegister.Title;
           row["FirstName"] = string.IsNullOrEmpty(apiRegister.First_Name) ? DBNull.Value : apiRegister.First_Name;
           row["Initials"] = string.IsNullOrEmpty(apiRegister.Initials) ? DBNull.Value : apiRegister.Initials;
           row["Surname"] = string.IsNullOrEmpty(apiRegister.Surname) ? DBNull.Value : apiRegister.Surname;
           row["Suffix"] = string.IsNullOrEmpty(apiRegister.Suffix) ? DBNull.Value : apiRegister.Suffix;
           row["IneligibleReason"] = string.IsNullOrEmpty(apiRegister.IneligibleReason) ? DBNull.Value : apiRegister.IneligibleReason;
           row["Markers"] = string.IsNullOrEmpty(apiRegister.Markers) ? DBNull.Value : apiRegister.Markers;
           row["Nationality"] = string.IsNullOrEmpty(apiRegister.Nationality) ? DBNull.Value : apiRegister.Nationality;
           row["DOB"] = apiRegister.DOB is null ? DBNull.Value : apiRegister.DOB;
           row["DateOfAttainment"] = apiRegister.Date_Of_Attainment is null ? DBNull.Value : apiRegister.Date_Of_Attainment;
           row["OtherElector"] = Convert.ToBoolean(apiRegister.OtherElector);
           row["StreetName"] = string.IsNullOrEmpty(apiRegister.StreetName) ? DBNull.Value : apiRegister.StreetName;
           row["LocalityAddress1"] = string.IsNullOrEmpty(apiRegister.LocalityAddress1) ? DBNull.Value : apiRegister.LocalityAddress1;
           row["LocalityAddress2"] = string.IsNullOrEmpty(apiRegister.LocalityAddress2) ? DBNull.Value : apiRegister.LocalityAddress2;
           row["LocalityAddress3"] = string.IsNullOrEmpty(apiRegister.LocalityAddress3) ? DBNull.Value : apiRegister.LocalityAddress3;
           row["PostCode"] = string.IsNullOrEmpty(apiRegister.Postcode) ? DBNull.Value : apiRegister.Postcode;
           row["AbsentVoteAddress1"] = string.IsNullOrEmpty(apiRegister.ava1) ? DBNull.Value : apiRegister.ava1;
           row["AbsentVoteAddress2"] = string.IsNullOrEmpty(apiRegister.ava2) ? DBNull.Value : apiRegister.ava2;
           row["AbsentVoteAddress3"] = string.IsNullOrEmpty(apiRegister.ava3) ? DBNull.Value : apiRegister.ava3;
           row["AbsentVoteAddress4"] = string.IsNullOrEmpty(apiRegister.ava4) ? DBNull.Value : apiRegister.ava4;
           row["AbsentVoteAddress5"] = string.IsNullOrEmpty(apiRegister.ava5) ? DBNull.Value : apiRegister.ava5;
           row["AbsentVoteAddress6"] = string.IsNullOrEmpty(apiRegister.ava6) ? DBNull.Value : apiRegister.ava6;
           row["AbsentVotePostcode"] = string.IsNullOrEmpty(apiRegister.avap) ? DBNull.Value : apiRegister.avap;
           row["ProxyName"] = string.IsNullOrEmpty(apiRegister.ProxyName) ? DBNull.Value : apiRegister.ProxyName;
           row["CreatedDateTime"] = DateTime.UtcNow;
           row["UpdatedDateTime"] = DateTime.UtcNow;

           registerTable.Rows.Add(row);
        }
    }

    return registerTable;
}