Recommend usage of temp table or table variable in

问题:

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.

回答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.

回答2:

I'm assigning Marcs response as the answer but I'd just like to give a little detail on how we implemented the requirement. Marc response helped greatly in the formulation of our solution.

We had to deal with an aim/guideline to keep within the Entity Framework while not utilizing SPROCS and although our solution may not suit others it has worked for us

We created a Item table in the Database with BatchId [uniqueidentifier] and ItemId varchar columns.

This table was added to the EF model so we did not use temporary tables.

On upload of these Ids this table is populated with the Ids [Inserts are quick enough we find using EF]

We then use context.ExecuteStoreCommand to run the SQL to do join the item table and the main table and update the bit field in the main table for records that exist for the batch Id created specifically for that session.

We finally clear this table for that batchId.

We have the performance, keeping within our no SPROC goal. [Which not of us agree with :) but its a democracy]

Our exact requirements are a little more complex but insofar as needing good update performance using the Entity framework given our specific restrictions it works fine.

Liam