Revisions to Fastest Way of Inserting in Entity Framework

deleted 71 characters in body

edited Jul 18, 2012 at 10:43

3.6k
3
27
46

The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insertusing intoEntity some_staging_table..Framework. 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insert into some_staging_table... 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

insert into some_staging_table using Entity Framework. -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table

deleted 31 characters in body

Source Link

edited May 11, 2011 at 11:58

Simon Hughes

3.6k
3
27
46

We had the exact same problem. The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insert into some_staging_table... 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

We had the exact same problem. The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insert into some_staging_table... 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insert into some_staging_table... 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

Source Link

answered May 9, 2011 at 17:32

Simon Hughes

3.6k
3
27
46

We had the exact same problem. The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.

ie.

begin tran insert into some_staging_table... EF insert SQL insert into some_staging_table... 4000 times -- Single insert into main table (this could be a tiny stored proc call) insert into some_main_already_large_table (columns...) select (columns...) from some_staging_table truncate table some_staging_table commit

Collectives™ on Stack Overflow

Return to Answer