推荐在实体框架4中使用临时表或表变量。更新性能实体框架 [英] Recommend usage of temp table or table variable in Entity Framework 4. Update Performance Entity framework

问题描述

我需要更新表中的位字段并将该表中特定ID的列表的此字段设置为true。

I need to update a bit field in a table and set this field to true for a specific list of Ids in that table.

ID从

我想在纯SQL中，最有效的方法是创建一个临时表并用ID填充它，然后将主表与

I guess in pure SQL the most efficient way would be to create a temp table and populate it with the Ids, then join the main table with this and set the bit field accordingly.

我可以创建一个SPROC来接收ID，但是可能涉及200-300,000行，需要设置该标志，因此它可能不是最有效的方法。使用IN语句会限制可传递的数据量和性能。

I could create a SPROC to take the Ids but there could be 200 - 300,000 rows involved that need this flag set so its probably not the most efficient way. Using the IN statement has limitation wrt the amount of data that can be passed and performance.

如何使用实体框架实现上述目标

How can I achieve the above using the Entity Framework

我想可以创建一个SPROC来创建一个临时表，但这从模型的角度来看是不存在的。

I guess its possible to create a SPROC to create a temp table but this would not exist from the models perspective.

有没有办法在运行时动态添加实体。 [或者这种方法会引起头痛]。

Is there a way to dynamically add entities at run time. [Or is this approach just going to cause headaches].

我在上面做出了一个假设，尽管用300,000行填充一个临时表并进行联接会更快。比打电话给SPROC 300,000次：）
[Ids是向导]

I'm making the assumption above though that populating a temp table with 300,000 rows and doing a join would be quicker than calling a SPROC 300,000 times :) [The Ids are Guids]

还有其他我应该考虑的方法。

Is there another approach that I should consider.

推荐答案

对于30万行之类的数据量，我会忘记EF。我可以通过以下表格来做到这一点：

For data volumes like 300k rows, I would forget EF. I would do this by having a table such as:

BatchId  RowId

其中RowId是我们要更新的行的PK，而BatchId仅指此运行的30万行（一次允许多个）。

Where RowId is the PK of the row we want to update, and BatchId just refers to this "run" of 300k rows (to allow multiple at once etc).

我将生成一个新的BatchId（这可能是唯一的-Guid会引起注意），并使用SqlBulkCopy将te记录插入到该表中，即

I would generate a new BatchId (this could be anything unique -Guid leaps to mind), and use SqlBulkCopy to insert te records onto this table, i.e.

100034   17
100034   22
...
100034   134556

然后我将使用一个simgle sproc进行联接和更新（并从表中删除该批处理）。

I would then use a simgle sproc to do the join and update (and delete the batch from the table).

SqlBulkCopy是将大量数据发送到服务器的最快方法；您不会在往返中淹死。 EF是面向对象的：非常适合许多场景-但是不适合这种情况。

SqlBulkCopy is the fastest way of getting this volume of data to the server; you won't drown in round-trips. EF is object-oriented : nice for lots of scenarios - but not this one.

这篇关于推荐在实体框架4中使用临时表或表变量。更新性能实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！