加速LINQ插入操作

Question

我有一个CSV文件，需要将其插入到SQL Server数据库中。是否有一种方法可以加快LINQ插入的速度？

我创建了一个简单的Repository方法来保存记录：

    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 用于 select if exists，新 SqlCommand 用于 update/insert）的方式要慢得多。

对于 100k 个 csv 行，它需要大约一个小时，而 ADO.net 的方式只需要1分钟左右。对于 2M csv 行，ADO.net 大约需要20分钟。LINQ 在25分钟内添加了其中的30k行。我的数据库有3个表，在dbml中连接，但其他两个表都是空的。所有测试都是在空表的情况下进行的。

P.S. 我已经尝试使用 SqlBulkCopy，但我需要对 Offer 进行一些转换，然后才能将其插入到数据库中，我认为这违背了 SqlBulkCopy 的目的。

更新/编辑： 18小时后，LINQ 版本仅添加了约200K行。

我还尝试过仅使用 LINQ 插入进行导入，但与 ADO.net 相比也非常慢。我没有看到仅插入/submitchanges 和 select/update/insert/submitchanges之间的巨大差异。

我还需要尝试批量提交、手动连接到数据库和编译查询。

- Marius

---

PK（offerId）是否为标识？ - Paul Suart

我对Linq to Sql还比较新，但除非我弄错了，dbOffer = offer; 这一行并不对应于数据库的更新，而仅仅是将本地变量 dbOffer 的引用进行更新 - 而这个变量之后再也没有被使用过... - Eamon Nerbonne

如果您知道系统在等待什么，即瓶颈在哪里（至少与ADO.NET相比变得更糟的是什么），例如高I/O等待时间、高CPU时间、更高的网络流量，那么减速的原因就可以更可靠地进行调试。 - Eamon Nerbonne

SubmitChanges不会批量插入，我认为这是性能问题的根源。相反，使用ADO，或者将记录作为XML或表变量发送到存储过程并进行批量插入。 - waterlooalex

@Marius，没有什么能阻止你使用SqlBulkCopy进行转换，我能够在不到7分钟的时间内加载整个SO数据库（数百万条记录）并对所有问题执行标签拆分。请参见：http://github.com/SamSaffron/So-Slow/tree/master - Sam Saffron

Answer 1

SubmitChanges不会批量处理更改，它每个对象执行一个单独的插入语句。如果想要进行快速插入操作，我认为你需要停止使用LINQ。

在SubmitChanges执行时，可以启动SQL Profiler并观察正在执行的SQL语句。

参见这里的问题“LINQ to SQL是否可以执行批量更新和删除？或者它总是一次更新一行？”：http://www.hookedonlinq.com/LINQToSQLFAQ.ashx

它链接到这篇文章：http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx，该文章使用扩展方法来解决LINQ无法批量插入、更新等的问题。

- Alex Black

---

是的，批量插入确实是最好的选择。或者让LINQ在夜间运行。 - Kirk Broadhurst

@Alex、@Kirk和@Marius，我找不到任何可以修复第二篇文章中批量插入的实现（批量更新和删除……）。你们能否给出建议？它可以很好地批量更新和删除，但我急需批量插入。使用SqlBulkCopy很好，但在大量表格中进行手动同步/跟踪我的自动身份字段（主键）的插入后，用于我的关联表格是相当麻烦的。非常感谢您的任何建议。 - Fadrian Sudaman

@FadrianSudaman 请查看我发布的批量插入实现，它可以轻松地添加到自动生成的实体作为一个部分类：https://dev59.com/cWox5IYBdhLWcg3wbDok#11974858 - Andrew Mao

@AndrewMao 我看不出你的帖子如何解决我的问题。是的，它确实使使用批量插入更加容易和清晰，但我在这里提出的问题涉及在批量插入后同步自动生成的 PK。 - Fadrian Sudaman

@DiogoCid，当我写这条消息的时候，链接确实可以使用，那是8年前的事情了。 - waterlooalex

Answer 2

你尝试过将插入操作放在事务中，并/或者延迟 db.SubmitChanges 以便批量处理多个插入操作吗？

事务通过减少 fsync() 的需求来提高吞吐量，而延迟 db.SubmitChanges 则可以减少 .NET<->db 往返次数。

编辑：参见 http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html 获取更多优化原则。

- Eamon Nerbonne

---

我还没有尝试过任何方法。我正在尝试找出如何加快速度的选项... - Marius

我正要尝试类似的项目，对我非常有效的方法（尽管是sqlite）是使用触发器，将插入转换为更新，当已存在插入行时 - 这样就可以避免检查要插入的行是否已经存在于数据库中。 - Eamon Nerbonne

好的建议。也许可以让你的方法接受一个IEnumerable<Offer>来进一步强制执行批处理？ - jeremyalan

将批处理包装在事务中不会有太大变化，我已经测试了50000行数据，每500行执行一次SubmitChanges()，与非事务代码相比，差异仅约为10秒，而非事务代码需要约360秒。 - ViRuSTriNiTy

@ViRuSTriNiTy：事务是否有帮助取决于几个细节，其中最重要的是涉及到的数据库引擎、隔离级别是什么、是否受CPU限制（在客户端还是服务器上）或I/O限制等。你是说在某种情况下，使用每500行批处理一个事务与没有事务的批处理在速度上没有实质性区别？ - Eamon Nerbonne

@EamonNerbonne 是的，这就是为什么我描述了我的测试用例。当然，在另一种情况下会有所不同。 - ViRuSTriNiTy

Answer 3

请查看以下页面，了解如何使用批量插入（Bulk Insert）代替LINQ的InsertOnSubmit()函数。只需要在您的代码中添加提供的BulkInsert类，并进行一些微小的更改，就可以大大提高性能。Mikes Knowledge Base - BulkInserts with LINQ祝你好运！

- Mike Gledhill

Answer 4

我想知道你是否遇到了数据集积累在数据上下文中导致解析行对内部标识缓存的速度慢（在SingleOrDefault期间只检查一次，并且对于“未命中”，当实体实现时，我期望看到第二个命中）。

我不确定短路是否适用于 SingleOrDefault（尽管在.NET 4.0中是适用的）。

我建议每进行 n 次操作就放弃数据上下文（提交更改并替换为空的数据上下文），其中 n 可能是250或其他值。

---

考虑到您目前是每个实例调用SubmitChanges，您可能也在浪费大量时间检查差异 - 如果您只更改了一行，则是无意义的。仅按批次而不是每个记录调用SubmitChanges。

- Marc Gravell

---

这很可能是问题所在。寻找高CPU使用率。 - usr

Answer 5

Alex给出了最佳答案，但我认为有一些事情被忽视了。

你在这里面遇到的一个主要瓶颈是针对每个项目单独调用SubmitChanges。我认为大多数人并不知道的一个问题是，如果您没有手动打开您的DataContext连接，那么DataContext将重复打开和关闭它自己。然而，如果你自己打开它，然后在你完全完成之后自己关闭它，事情会运行得更快，因为它不必每次重新连接到数据库。当我尝试找出为什么DataContext.ExecuteCommand()在执行多个命令时如此慢时，我发现了这一点。

您可以加快速度的几个其他区域：

虽然Linq To SQL不支持您直接进行批量处理，但应该先分析所有内容，然后再等待调用SubmitChanges()。您不需要在每个InsertOnSubmit调用之后调用SubmitChanges()。

如果实时数据完整性不是非常关键，您可以在开始检查是否已存在优惠之前从服务器检索offer_id列表。这可以显著减少您调用服务器以获取不存在的现有项目的次数。

- rossisdead

---

LINQ 只有在插入时才会变慢。我进行的第一个测试只是使用 InsertOnSubmit/SubmitChanges。 - Marius

是的，那部分仍然会很慢，因为Linq也会返回任何数据，包括数据库生成的数据。 - rossisdead

我意识到LINQ-to-SQL并不是批量插入的最佳工具。然而，您手动打开/关闭连接的建议导致CPU利用率更低，性能更好。 - Mayo

Answer 6

为什么不将offer[]传递到该方法中，在提交到数据库之前在缓存中进行所有更改。或者您可以使用组进行提交，以便您不会用尽缓存。最重要的是发送数据的时间，最浪费时间的是连接的关闭和打开。

- Yuriy Faktorovich

Answer 7

将此转换为编译查询是我能想到的提高性能最简单的方法：

更改以下内容：

    Offer dbOffer = this.db.Offers.SingleOrDefault (
         o => o.offer_id == offer.offer_id);

to:

Offer dbOffer = RetrieveOffer(offer.offer_id);

private static readonly Func<DataContext, int> RetrieveOffer
{
   CompiledQuery.Compile((DataContext context, int offerId) => context.Offers.SingleOrDefault(o => o.offer_id == offerid))
}

这个改变单独来看可能无法让它像你的ado.net版本一样快，但它会是一个重大改进，因为没有编译查询，每次运行此方法时都会动态构建表达式树。

正如一位发帖者已经提到的那样，如果您想要最佳性能，您必须重构您的代码，以便仅在调用提交更改时调用一次。

- Dan Ling

Answer 8

你真的需要在将记录插入数据库之前检查它是否存在吗？我认为这看起来很奇怪，因为数据来自csv文件。

顺便说一下，我已经尝试过使用SqlBulkCopy，但是我需要对Offer进行一些转换后再将其插入数据库，我认为这违背了SqlBulkCopy的初衷。

我不认为这会完全违背初衷，为什么会呢？只需用所有来自csv的数据填充一个简单的数据集，然后执行SqlBulkCopy即可。我曾经用一个包含30000多行的集合做过类似的事情，导入时间从几分钟变成了几秒钟。

- terjetyl

---

我需要从CSV中添加/更新日期。我的原始文件比30k行还要大，而且我需要每天/每周这样做。将所有内容加载到数据库中再选择每一行会更慢。 - Marius

Answer 9

我怀疑耗时较长的不是插入或更新操作，而是用于确定您的报价是否已存在的代码：

Offer dbOffer = this.db.Offers.SingleOrDefault (
         o => o.offer_id == offer.offer_id);

如果你想要优化这个，我认为你会走上正确的道路。也许可以使用 Stopwatch 类来进行一些计时，以帮助证明我的观点是否正确。

通常情况下，在不使用 Linq-to-Sql 的情况下，你需要一个插入/更新过程或 SQL 脚本来确定你传递的记录是否已经存在。你正在使用 Linq 进行这个昂贵的操作，这肯定无法与本地 SQL 的速度相匹配（当你使用 SqlCommand 并选择记录是否存在时，就是发生了这种情况），在主键上查找。

- Paul Suart

---

ADO可能有相同的问题。尽管你的评论仍然很有帮助。 - usr

啊，但是ADO方法要快得多，因为你让数据库做它最擅长的事情。遍历IEnumerable以查看是否存在具有相同Id的项将会慢得多。 - Paul Suart

Answer 10

这段代码运行良好，可以防止大量数据：

if (repository2.GeoItems.GetChangeSet().Inserts.Count > 1000)
{
    repository2.GeoItems.SubmitChanges();
}

然后，在批量插入结束时，使用这个：

repository2.GeoItems.SubmitChanges();

- mariano