我有一个插入大量数据到SQL Server的问题。
以前我正在使用实体框架，但是仅100k根级记录（分别包含两个不同的集合，每个集合都在200k记录上进一步运行）=大约500k-600k记录中的记录。在这里，我应用了所有优化（例如AutoDetectChangesenabled = false，并在每批之后重新创建和处理上下文。）
我拒绝了该解决方案，并使用了非常快速且高效的Bulkinsert。只能在一分钟左右的时间内插入100k记录。
，但主要问题是从新插入的记录中恢复主要键。为此，我正在考虑编写可以在TVP上操作的存储过程（即，在存储器数据表中保留所有root Level 100K记录 ）。在其中，我将使用输出插入。ID以获取应用程序中的所有主要键）。
因此，如何将此方法（即存储过程中的SQL插入查询）与SQLBulkCopy方法进行比较。
是否知道，如果某种程度上，我可以在SQLBulkCopy操作后将所有主键恢复吗？或有关插入输出的具体的东西。ID将在应用程序中返回所有正确的新键。
ps：我不想在此过程中创建任何登台表。这只是一个开销。
这是一个基于评论中的讨论/扩展此处提到的想法的示例：可以在SQL Bulkcopy之后恢复PrimayKey ID？
即。
- 做一个从C＃上传到SQL 中的临时表
- 使用SQL将数据从临时表复制到实际表（在这一点上生成ID），然后返回IDS。
我没有机会对此进行测试，但希望这会有所帮助：
//using System.Data.SqlClient;
//using System.Collections.Generic;
public DataTable CreatePersonDataTable(IEnumerable<PersonDTO> people)
{
//define the table
var table = new DataTable("People");
table.Columns.Add(new DataColumn("Name", typeof(string)));
table.Columns.Add(new DataColumn("DOB", typeof(DateTime)));
//populate it
foreach (var person in people)
{
table.Rows.Add(person.Name, person.DOB);
}
return table;
}
readonly string ConnectionString; //set this in the constructor
readonly int BulkUploadPeopleTimeoutSeconds = 600; //default; could override in constructor
public IEnumerable<long> BulkUploadPeople(IEnumerable<PersonDTO> people) //you'd want to break this up a bit; for simplicty I've bunged everything into one big method
{
var data = CreatePersonDataTable(people);
using(SqlConnection con = new SqlConnection(ConnectionString))
{
con.Open(); //keep same connection open throughout session
RunSqlNonQuery(con, "select top 0 Name, DOB into #People from People");
BulkUpload(con, data, "#People");
var results = TransferFromTempToReal(con, "#People", "People", "Name, DOB", "Id");
RunSqlNonQuery(con, "drop table #People"); //not strictly required since this would be removed when the connection's closed as it's session scoped; but best to keep things clean.
}
return results;
}
private void RunSqlNonQuery(SqlConnection con, string sql)
{
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
private void BulkUpload(SqlConnection con, DataTable data, string targetTable)
{
using(SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
bulkCopy.BulkCopyTimeout = 600; //define this in your config
bulkCopy.DestinationTableName = targetTable;
bulkCopy.WriteToServer(data);
}
}
private IEnumerable<long> TransferFromTempToReal(SqlConnection con, string tempTable, string realTable, string columnNames, string idColumnName)
{
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = string.Format("insert into {0} output inserted.{1} select {2} from {3}", realTable, idColumnName, columnNames, tempTable);
using (SqlDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
yield return r.GetInt64(0);
}
}
}
}
在您的问题中，您还补充说，您不想使用登台表，因为它是"开销" ...请尝试。您可能会发现创建登台表的小开销小于使用此方法的性能增益。
显然，它不会像插入和忽略返回的ID一样快。但是，如果这是您的要求，在没有其他答案的情况下，这可能是最好的选择。
任何想法，如果以某种方式，我可以在sqlbulkcopy之后恢复所有主要钥匙 操作
你不能。没有办法直接从sqlbulkcopy进行。
ps：我不想在此过程中创建任何登台表。这 只是一个开销。
不幸的是，如果您想恢复主要钥匙，则需要执行或使用其他方法（如您建议的TVP）。
免责声明：我是实体框架扩展的所有者
一种替代解决方案是使用已经为实体框架支持Bulkinsert的库。在引擎盖下，它是使用sqlbulkcopy 分期表。
默认情况下，Bulkinsert方法已经输出了主要键值。
库不是免费的，但是，它为您的公司增加了一些灵活性，您将不必代码/支持任何内容。
示例：
// Easy to use
context.BulkSaveChanges();
// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);
// Customize Primary Key
context.BulkMerge(customers, operation => {
operation.ColumnPrimaryKeyExpression =
customer => customer.Code;
});