加快LINQ插入 [英] Speed up LINQ inserts
问题描述
我有一个CSV文件，我不得不将其插入到一个SQL Server数据库。有没有一种方法，以加快LINQ插入？
I have a CSV file and I have to insert it into a SQL Server database. Is there a way to speed up the LINQ inserts?
我创建了一个简单的资源库的方法保存记录：
I've created a simple Repository method to save a record:
public void SaveOffer(Offer offer)
{
Offer dbOffer = this.db.Offers.SingleOrDefault (
o => o.offer_id == offer.offer_id);
// add new offer
if (dbOffer == null)
{
this.db.Offers.InsertOnSubmit(offer);
}
//update existing offer
else
{
dbOffer = offer;
}
this.db.SubmitChanges();
}
但是，使用这种方法，程序的方式要慢得多然后用ADO.net SQL插入（新的SqlConnection，用于选择是否存在，对于更新/插入新的SqlCommand新的SqlCommand）插入数据。
But using this method, the program is way much slower then inserting the data using ADO.net SQL inserts (new SqlConnection, new SqlCommand for select if exists, new SqlCommand for update/insert).
在10万CSV行大约需要一个小时VS 1分钟左右的ADO.net方式。对于2M CSV行花了ADO.net约20分钟。 LINQ加入有关在25分钟内的2M行30K。我的数据库有3个表，在DBML联系，但其他两个表是空的。这些测试用的所有表空进行。
On 100k csv rows it takes about an hour vs 1 minute or so for the ADO.net way. For 2M csv rows it took ADO.net about 20 minutes. LINQ added about 30k of those 2M rows in 25 minutes. My database has 3 tables, linked in the dbml, but the other two tables are empty. The tests were made with all the tables empty.
P.S。我试着使用SqlBulkCopy的，但我需要将其插入到数据库之前，做提供一些转变，我认为违背了SqlBulkCopy的目的。
P.S. I've tried to use SqlBulkCopy, but I need to do some transformations on Offer before inserting it into the db, and I think that defeats the purpose of SqlBulkCopy.
更新/编辑： 18小时后，LINQ的版本仅增加了〜200K行。
Updates/Edits: After 18hours, the LINQ version added just ~200K rows.
我测试过的进口刚刚与LINQ插入过了，也实在是太慢了与ADO.net比较。我还没有看到刚刚插入/的SubmitChanges和选择/更新/插入/的SubmitChanges之间有很大的区别。
I've tested the import just with LINQ inserts too, and also is really slow compared with ADO.net. I haven't seen a big difference between just inserts/submitchanges and selects/updates/inserts/submitchanges.
我还是要尽量批量提交，手动连接到数据库，并编译查询。
I still have to try batch commit, manually connecting to the db and compiled queries.
推荐答案
的SubmitChanges不批的变化，但它确实每个对象单个插入语句。如果你想要做的快速插入，我想你需要停止使用LINQ。
SubmitChanges does not batch changes, it does a single insert statement per object. If you want to do fast inserts, I think you need to stop using LINQ.
在的SubmitChanges正在执行，启动SQL事件探查器，看着正在执行的SQL。
While SubmitChanges is executing, fire up SQL Profiler and watch the SQL being executed.
请参阅问题可以的LINQ to SQL执行批量更新和删除？或者它总是这样一行更新的时间？这里：<一href="http://www.hookedonlinq.com/LINQToSQLFAQ.ashx">http://www.hookedonlinq.com/LINQToSQLFAQ.ashx
See question "Can LINQ to SQL perform batch updates and deletes? Or does it always do one row update at a time?" here: http://www.hookedonlinq.com/LINQToSQLFAQ.ashx
它链接到这篇文章：<一个href="http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx">http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx使用扩展方法来解决LINQ无力批量插入和更新等。
It links to this article: http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx that uses extension methods to fix linq's inability to batch inserts and updates etc.
这篇关于加快LINQ插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
