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

问题描述

我需要更新一个表中的一个位字段，并将该字段设置为该表中特定的Id列表的true。

Ids被传入一个外部过程。

我在纯SQL中猜测最有效的方法是创建一个临时表并使用Id填充它，然后加入主表，相应地设置位字段。

我可以创建一个SPROC来获取Ids，但是可能需要200 - 300,000行，需要这个标志集，所以它可能不是最多的高效的方式。使用IN语句限制了可以传递的数据量和性能。

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

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

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

我正在做上述假设，虽然填充了一个30万行的临时表，做一个连接会更快比起拨打SPROC 30万次:)
[Ids是Guids]

还有另一种方法我应该考虑。

提前感谢
Liam

解决方案

对于300k行的数据卷，我会忘记EF我可以通过下列方式来执行此操作：

  BatchId RowId 
  pre> 
 
 
其中RowId是我们要更新的行的PK，而BatchId只是指这个运行300k行（允许多个等）。 
 
 
 
我会生成一个新的BatchId（这可能是任何独特的 - 可以跳过头脑），并使用SqlBulkCopy将te记录插入到此表，即
  100034 17 
 100034 22 
 ... 
 100034 134556 
  
然后我将使用一个simgle sproc来进行连接和更新（并从表中删除批处理）。
 
 
 
 SqlBulkCopy是将该数据量提取到服务器的最快方法;你不会淹没在往返。 EF是面向对象的：对于许多场景而言很好 - 但不是这样。
 
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.

The Ids are passed in from an external process.

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.

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

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].

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.

Thanks in advance,
Liam
解决方案
For data volumes like 300k rows, I would forget EF. I would do this by having a table such as:
BatchId  RowId
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).

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
I would then use a simgle sproc to do the join and update (and delete the batch from the table).

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屋！