Entity Framework 7 Preview 6 has just been released (12th July 2022). Shay from the Entity Framework team has done an awesome write up on the different performance improvements included in EF 7.
One of the improvements is that EF 7 will remove transactions for single insert as they are not needed. This straight away saves two roundtrips associated with starting and committing a transaction. This means the performance benefit of this change will increase as network latency between an app running EF and the DB increases.
Below I’ve ran three benchmarks with BenchmarkDotNet comparing the times of a simple single insert in EF 6.07 with those of EF 7 Preview 6. The three benchmarks correspond to when the benchmark console app was running in the North Europe (Dublin) Azure region and the DB …
- was on localhost
- remote but same Azure region (North Europe) and
- remote but different Azure region (North Europe -> East US)
Click on the image for a larger view in a new tab…
For the 1st benchmark, due to the DB being on the same box as the benchmark app, latency is tiny so performance savings are minimal. Above we can see a 6% improvement.
The second benchmark still has very low latency but would be quite representative of a real production setup whereby our app and DB aren’t on the same box but are in the same Azure region or general geographic region. The benchmark I ran showed EF 7 (Preview 6) performed 13% faster than EF 6.07 even in a low latency environment 💪🏻 …
For the third benchmark I created a DB in the East US region and given that the benchmark app is in North Europe (Dublin) we should have quite a bit more latency than in the second benchmark.
According to the region -> region latency figures shown below (which are taken from this page) …
… as of May 2021 average round trip time between North Europe and East US is 72 milliseconds which is quite slow… In this case due to the removed transaction related roundtrips EF 7 performs the single insert 65% quicker than EF 6, that’s amazing 💪🏻 …
Entity Framework 7 multiple insert performance improvements
Apart from removing unnecessary roundtrips for a single insert as discussed above the EF team have also optimized the MERGE statement which is used to insert multiple records.
The MERGE statement used for inserts has been changed to not use a temporary table (which is an optimization in itself) and to use a simplified OUTPUT clause. These changes mean EF 7 can also get rid of the two transaction roundtrips when inserting multiple records as long as the amount of records is less than or equal to the batch size.
Below for example we can see two transaction roundtrips for begin and commit in Entity Framework 6 for 42 records while in the Entity Framework 7 version there are none….
Click on the image for a larger view in a new tab…
We can see for 43 records since we are sending the inserts in two separate commands Entity Framework 7 does of course still need explicit transactions to be sent. Two less roundtrips is a big deal so consider increasing batch size to take account of this optimization.
Removal of the explicit transaction begin and commit roundtrips and of the MERGE temporary table results in significant performance improvements for Entity Framework 7 when insert multiple records when the record amount is <= batch size.
Below we can see improvements of 23%, 40% and 65% going from top to bottom, which correspond to localhost, remote (low latency) and remote (high latency) respectively when doing 10 simple inserts such as…
Click the image for a larger view in a new tab…
Note however though amending of the MERGE statement like this is a BREAKING CHANGE for those that have triggers defined on tables…
Thankfully there will be an easy opt out for affected users…
Single insert commands when parent using GUID or HILO ID
Apart from above Entity Framework has also optimised the SQL generated when adding a child to a parent such as below when the parent doesn’t use Identity IDs…
In Entity Framework 6 if a parent entity (blog in the case above) used an ID generated on the client side such as GUID or HILO Entity Framework would still generate two separate insert commands.
This is not required as since the Blog entity is not using Identity EF doesn’t need to wait for the DB to return the new Blog record ID before sending the post insert. In EF 7 these two INSERTs are combined into a single command, reducing a roundtrip.
Would you consider switching to GUID or HILO to take advantage of this optimization? Entity Framework and HILO are pretty easy to use together.
Entity Framework performance improves with each release
What do you think about these improvements? Awesome right?
I’m really excited to get using Entity Framework 7.
The product is improving all the time 💪🏻 …
Hopefully one day I will be able to stop defending it to devs who say things like ORMs will always generate less efficient SQL than someone writing it manually. This simply isn’t true.
ORMs have teams of developers working on them and many like EF are open source so have large amounts of community contributions too. This means in reality there’s every chance an ORM like Entity Framework could in fact generate more efficient SQL than a sole developer doing it themselves manually.
Shay from the Entity Framework team notes this in his EF performance blog post when referring how EF uses MERGE (originally intended for merging together two tables) for inserts…
Thanks