I'm looking for the fastest way of inserting into Entity Framework.
I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can potentially last more than 10 minutes (default timeout of transactions), and this will lead to an incomplete transaction.
I have made an generic extension of @Slauma s example above;
Usage:
All the solutions written here don't help because when you do SaveChanges(), insert statements are sent to database one by one, that's how Entity works.
And if your trip to database and back is 50 ms for instance then time needed for insert is number of records x 50 ms.
You have to use BulkInsert, here is the link: https://efbulkinsert.codeplex.com/
I got insert time reduced from 5-6 minutes to 10-12 seconds by using it.
I've investigated Slauma's answer (which is awesome, thanks for the idea man), and I've reduced batch size until I've hit optimal speed. Looking at the Slauma's results:
It is visible that there is speed increase when moving from 1 to 10, and from 10 to 100, but from 100 to 1000 inserting speed is falling down again.
So I've focused on what's happening when you reduce batch size to value somewhere in between 10 and 100, and here are my results (I'm using different row contents, so my times are of different value):
Based on my results, actual optimum is around value of 30 for batch size. It's less than both 10 and 100. Problem is, I have no idea why is 30 optimal, nor could have I found any logical explanation for it.
Try to use a Stored Procedure that will get an XML of the data that you want to insert.
Use stored procedure that takes input data in form of xml to insert data.
From your c# code pass insert data as xml.
e.g in c#, syntax would be like this:
To your remark in the comments to your question:
That's the worst thing you can do! Calling
SaveChanges()
for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:SaveChanges()
once after ALL records.SaveChanges()
after for example 100 records.SaveChanges()
after for example 100 records and dispose the context and create a new one.For bulk inserts I am working and experimenting with a pattern like this:
I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.
For the performance it is important to call
SaveChanges()
after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites,SaveChanges
doesn't do that, the entities are still attached to the context in stateUnchanged
. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.Here are a few measurements for my 560.000 entities:
The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.