Recommend usage of temp table or table variable in Entity Framework 4. Update Performance Entity framework

Question

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.

Answer 1

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.