实体框架6插入太慢 [英] Entity Framework 6 insertion is too slow

问题描述

我有一个数据源，我必须解析并将结果插入到sql server中。由于解析速度如此之快，我认为开销显然与实体框架有关。

我需要将数据插入几十个表中，其中大约有80个。每个表有8列，比如平均而言，其中有一个自动增量id列。每次，我都需要插入几千行，比如20个表。因此
某些表格很大（可能是100k行？）

表格之间存在关系，这只是一种星形关系，如下所示：

 table5 table6 
 \ / 
 table3 ----------- \ \ / / ----- ------------ table4 
 table1 ------------记录表------------------ table2 

想象一下主表周围有80个表。 90％的关系是1对多，有些是多对多的，有限的一对一。

我插入的方式是，我将所有结果添加到上下文使用 AddRange 方法，我在全球范围内调用
SaveChanges 。

我在当前设置中观察到了插入效率，我发现插入开始需要几秒钟，然后是几分钟，现在需要30分钟才能大致以相同的大小插入数据。

所以我的问题是，是否有任何好方法可以提高插入速度？我知道批量插入，但我有继承关系所以我正在寻找任何其他不会破坏关系约束的方法。

任何好的建议或推荐的好的分析器？或者，如果我定义存储过程以满足插入目的，它会在这种情况下有所帮助吗？我很抱歉，但实际上我已经学会了几个星期，并期望它是一个用于业务逻辑的正确工具。
如果社区里有一些知名的东西，我就不用了。

解决方案

您好
HuStmpHrrr，

就像你提到的标题一样，你的案例更多与
实体框架6相关，我将把您的案例移至 数据平台开发  >  ADO.NET
实体框架和LINQ to Entities 论坛以获得更好的支持。

祝你有愉快的一天！

Kristin

Hi,

I have a data source that I have to parse and insert the results into the sql server. Since the parsing is so fast, I think the overhead is obviously related to entity framework.

I need to insert data into a few dozens of tables, around 80 of them. Each table has 8 columns, say, at average, among which there is an auto-incremental id column. And each time, I properly need to insert a few thousands rows into say, 20 tables. therefore some table are huge(maybe 100k rows?)

The tables have relation between them, which is simply a star form relation, like this:

                                   table5  table6
                                       \    /
           table3-----------\ \  / /-----------------table4
      table1 ------------ record table ------------------ table2 

just imagine the main table has 80 tables around it. 90% of the relation is 1-to-many, some many-to-many, limited one-to-one.

The way I do insertion is, I add all the result into context by using AddRange method, and I call SaveChanges once globally.

I observed my insertion efficiency in current settings, and I found at the very beginning, the insertion takes a few seconds, then minutes, now it takes 30 minutes to insert data roughly in the same size.

So my question is that is there any good way to boost the insertion speed up? I am aware of bulk insert, but I have inheritance relation so I am seeking any other way that is not going to break the relation constraint.

Any good suggestions or recommended good profiler? Or would it help in this situation if I define stored procedure to serve the insertion purpose? I am sorry but actually I just learned it for weeks and expecting it is a right tool to use for business logic. So bare with me if there is something out there that is well-known in community.

解决方案

Hi HuStmpHrrr,

Like your title mentioned, your case more related to Entity Framework 6, So I will move your case to Data Platform Development > ADO.NET Entity Framework and LINQ to Entities forum for better support.

Have a nice day!

Kristin

这篇关于实体框架6插入太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！