Entity Framework Core 7 Preview 6: Performance Improvements

Entity Framework Core 7 (EF7) Preview 6 was released and it contains a bunch of performance improvements by reducing unnecessary roundtrips to the database and by improved queries. Please note: most of the improvements here are tight to Microsoft SQL  Server.

First, let's start with a simple example.

Inserting a single record

Let's consider the following code that inserts a single record into the database.

ToDo toDo = new() { Title = "ToDo 1" };
await context.ToDos.AddAsync(toDo);
await context.SaveChangesAsync();

Now let's have a look at the SQL statements that are being executed when using both EF Core 6.0 and 7.0.

EF Core 6.0

dbug: 20/07/2022 07:49:14.672 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'ReadCommitted'.
...
info: 20/07/2022 07:49:14.748 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (24ms) [Parameters=[@p0='ToDo 1' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [ToDos] ([Title])
      VALUES (@p0);
      SELECT [Id]
      FROM [ToDos]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
...
dbug: 20/07/2022 07:49:14.779 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.

So what's happening here.

• Trip to the database to begin a transaction
• Trip to the database with 2 SQL statements (I am ignoring SET NOCOUNT ON;)
• Insert the record into the database
• Select the Id of the inserted record (that's because when the Primary Key is an integer, by default it's set up as an IDENTITY column and we may require it for application logic)
• Trip to the database to commit the transaction

A couple of interesting questions here. Do we need a transaction here that initiates 2 roundtrips to the database? Since there is only one atomic operation with a single statement, we really don't need it. It's either going to pass or fail and not going to cause any data integrity issue.

Now let's run the same code using EF Core 7.0.

EF Core 7.0

info: 20/07/2022 07:53:51.959 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (25ms) [Parameters=[@p0='ToDo 1' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [ToDos] ([Title])
      OUTPUT INSERTED.[Id]
      VALUES (@p0);

There's clearly a difference between the above SQL statement and the SQL statements generated when using EF Core 6.0.

• A single trip to the database and there's no transaction
• A new statement SET IMPLICIT_TRANSACTIONS OFF is executed. SQL Server has an opt-in “Implicit Transactions” mode, where executing a statement outside of a transaction won’t auto-commit, but instead implicitly start a new transaction. EF is disabling it so that the changes are getting saved. 
• Instead of inserting and then selecting the IDENTITY value, the new SQL uses an “OUTPUT clause” to tell SQL Server to send the value directly from the INSERT.

Now, let's have a look at a bit more advanced example. That is what if we are doing multiple inserts?

Inserting multiple records

for (int i = 1; i <= 4; i++)
{
    ToDo toDo = new() { Title = $"ToDo {i}" };
    await context.ToDos.AddAsync(toDo);
}
 
await context.SaveChangesAsync();

Now again let's compare the SQL Statements from both EF Core 6.0 and 7.0.

EF Core 6.0

dbug: 20/07/2022 09:22:18.194 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'ReadCommitted'.
...
info: 20/07/2022 09:22:18.259 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (27ms) [Parameters=[@p0='ToDo 1' (Size = 4000), @p1='ToDo 2' (Size = 4000), @p2='ToDo 3' (Size = 4000), @p3='ToDo 4' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
      MERGE [ToDos] USING (
      VALUES (@p0, 0),
      (@p1, 1),
      (@p2, 2),
      (@p3, 3)) AS i ([Title], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Title])
      VALUES (i.[Title])
      OUTPUT INSERTED.[Id], i._Position
      INTO @inserted0;
 
      SELECT [i].[Id] FROM @inserted0 i
      ORDER BY [i].[_Position];
...
dbug: 20/07/2022 09:22:18.318 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.

So what's happening here.

• Trip to the database to begin a transaction
• Trip to the database with 3 SQL statements
• Declare a temporary table @inserted0
• Single MERGE statement that uses an “OUTPUT clause” to insert the INSERTED.[Id] to temporary table @inserted0.
• SELECT the Ids from the temporary table @inserted0
• Trip to the database to commit the transaction

EF Core 7.0

info: 20/07/2022 09:25:56.651 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (22ms) [Parameters=[@p0='ToDo 1' (Size = 4000), @p1='ToDo 2' (Size = 4000), @p2='ToDo 3' (Size = 4000), @p3='ToDo 4' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      MERGE [ToDos] USING (
      VALUES (@p0, 0),
      (@p1, 1),
      (@p2, 2),
      (@p3, 3)) AS i ([Title], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Title])
      VALUES (i.[Title])
      OUTPUT INSERTED.[Id], i._Position;

Here again, we don't need a Transaction.

• A single trip to the database and there's no transaction
• A new statement SET IMPLICIT_TRANSACTIONS OFF is executed for the same reason mentioned in the initial scenario.
• No use of temporary tables here. A single MERGE statement that uses an “OUTPUT clause” to tell SQL Server to send back all the INSERTED.[Id].

As you can see, with EF Core 7 unnecessary roundtrips to the database are reduced, and generated SQL statements are improved giving us an improvement in the performance.

Read More,
   Announcing Entity Framework Core 7 Preview 6: Performance Edition

Happy Coding.

Regards,
Jaliya