Entity Framework Core Performance Test

A solution used to run performance tests against Entity Framework Core (using different strategies and databases) to determine the load that could be catered for when inserting records into a database that has an auto increment column, using API endpoints.

This is as a result of experiencing terrible performance and ZombieCheck() issues when using the default implementation of Entity Framework Core with a Microsoft SQL server that has an auto increment column.

TLDR: If you are planning to use Entity Framework Core with Microsoft SQL Server and an auto increment column in a Production environment it is highly recommended that you consider changing some of the default values. Either by disabling auto transactions in your Entity Framework solution, by changing your lock isolation behavior in code or the database or setting the auto increment column as the Primary Key.

• Entity Framework Core Performance Test
  • Features
  • Prerequisites
  • Stress Test
    • Default Implementation
    • Suggested Retry Policy
    • Custom Execution Strategy
    • Changing Transaction Isolation Level
    • Guid as Id
    • Stored Procedure
    • EF Core with MySql
    • Conclusion
  • Visual Studio Quick Start
    • Running Locally in Visual Studio - With Docker
    • Running Locally in Visual Studio - Without Docker
    • Application Settings
  • Resources

Features

This is a ASP .Net Core App. The solution has the following features:

• Basic API to insert records into a MS SQL database using 3 different techniques.
  • Inserting using EF Core with a table that has an auto increment column.
  • Inserting using EF Core with a table that has a Guid column.
  • Inserting using a Stored Procedure that inserts into a table that has an auto increment column.
• Basic API to insert records into a MySql database using a table that has an auto increment primary key.
• Using the artillery npm package to stress test the API's.

Prerequisites

• Visual Studio or any C# Code Editor
• Node.js
• Artillery
• Docker (Optional)

Stress Test

Artillery was used to test the load that can be handled by EF Core using different strategies and technologies.

Default Implementation

Simple API that inserts records into a MS SQL database using EF Core. The table has an auto increment Id column.

Test: 100 inserts per second for 5 seconds (Note: This load can be greatly reduced when connecting to a network based SQL server)

Test Results: Only 4 records created successfully.

Error:

 System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call

Suggested Retry Policy

Simple API that inserts records into a MS SQL database using EF Core. The table has an auto increment Id column. Based on the results above and as suggested by Microsoft the EnableRetryOnFailure execution policy was added.

 services.AddDbContext<TestDbContext>(builder => builder
                .UseSqlServer(Configuration.GetConnectionString("CRUD"), sqlOptions =>
                {
                    sqlOptions.EnableRetryOnFailure(
                         maxRetryCount: 2,
                         maxRetryDelay: TimeSpan.FromSeconds(30),
                         errorNumbersToAdd: null);
                }));

Test: 100 inserts per second for 5 seconds (Note: This load can be greatly reduced when connecting to a network based SQL server)

Test Results: Only 3 records created successfully.

Error:

 System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
         at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck()

Custom Execution Strategy

Simple API that inserts records into a MS SQL database using EF Core. The table has an auto increment Id column. Based on the results above and online research it was decided to test with a Custom Execution Strategy.

Note the error received with regards to ZombieCheck is either an error in the way Dependency Injection is handled in conjunction with EnableRetryOnFailure or a false positive in the error received. This becomes clear when adding a Custom Execution Strategy and receiving the correct error.

 services.AddDbContext<TestDbContext>(builder => builder
                .UseSqlServer(Configuration.GetConnectionString("CRUD"), sqlOptions =>
                {
                    sqlOptions.ExecutionStrategy(c =>
                        new CustomExecutionStrategy(c, 2, TimeSpan.FromSeconds(30)));
                }));

Test: 100 inserts per second for 5 seconds (Note: This load can be greatly reduced when connecting to a network based SQL server)

Test Results: Only 2 records created successfully.

Error:

  Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 110) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Changing Transaction Isolation Level

Simple API that inserts records into a MS SQL database using EF Core. The table has an auto increment Id column.

After discussions with Microsoft and further research the issue is as follows:

The deadlock is caused by the SELECT statement in Entity Framework to get the generated ID value. By default, it takes a shared lock which can deadlock with the exclusive locks held by concurrent UPDATE statements.

This can be avoided in 4 ways:

1. Creating the Auto Increment Column as a Primary Key solves the issue as this means the value is read from the Primary Key's Index and a lock isn't created. Note Databases like MySql only allow you to create an Auto Increment Field as a Primary Key.

   use test
   go
   create table AutoGenId
   (
     ID int identity PRIMARY KEY,
     FirstName nvarchar(50),
     Surname nvarchar(50),
     IDNumber nvarchar(20)
   )
   go

2. Running SaveChanges in a ReadUncommitted Transaction (Would have to be applied per transaction or Database interaction).

   context.Database.CreateExecutionStrategy().Execute(context, c =>
   {
       using var transaction = c.Database.BeginTransaction(IsolationLevel.ReadUncommitted);
       c.SaveChanges();
       transaction.Commit();
   });

3. Setting AutoTransactionsEnabled to false (This can be done on a global context level or per transaction but means transactions won't be created automatically).

   _context.Database.AutoTransactionsEnabled = false;

4. Enabling Read Committed Snapshot on the Database (Database change). This is a recommendation from Scaling .Net Core Applications for general performance that could assist on reading from the database as well.

   Note: This solution will have an impact on your database's temp DB Storage.

   ALTER DATABASE test
   SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
   GO

Test: 100 inserts per second for 5 seconds

Test Results: All 500 records created successfully.

Guid as Id

Simple API that inserts records into a MS SQL database using EF Core. The table has a Guid Id column. Based on the deadlock issue above it was decided to test with a Guid Id that is generated in code thus not creating a deadlock.

Test: 100 inserts per second for 5 seconds

Test Results: All 500 records created successfully.

Stored Procedure

Simple API that inserts records into a MS SQL database using EF Core. The table has an auto increment Id column. Based on the deadlock issue above it was decided to test the insert with a Stored Procedure hoping it would handle the deadlock issue better.

Test: 100 inserts per second for 5 seconds

Test Results: All 500 records created successfully.

EF Core with MySql

Simple API that inserts records into a MySql database using EF Core (Pomelo.EntityFrameworkCore.MySql). The table has an auto increment Id as a Primary Key. MySql only allows 1 auto increment column and it has to be a Key.

Test: 100 inserts per second for 5 seconds

Test Results: All 500 records created successfully.

Conclusion

If a table with an Auto Incremented Id column is used the load that the default implementation of EF Core and MS SQL can handle is extremely disappointing.It isn't close to the load that can be handled by a Stored Procedure unless the default transaction isolation levels are changed.

Visual Studio Quick Start

The following tutorial shows how to run locally.

Running Locally in Visual Studio - With Docker

1. Clone this repo and open the project in Visual Studio.

2. Open deployment folder in your terminal (Command Prompt) and run the following command:

   docker-compose up

3. Run the app with F5

4. Open testing/artillery folder in your terminal (Command Prompt) and run the following commands:

   npm install -g artillery
   artillery run ./MsSqlAutoGenId.yml
   artillery run ./MsSqlGuid.yml
   artillery run ./MsSqlStoredProcedure.yml
   artillery run ./MySqlAutoGenId.yml

Running Locally in Visual Studio - Without Docker

1. Clone this repo and open the project in Visual Studio.

2. Setup an MS SQL database and run the scripts in deployment/mssql-scripts in order.

3. Setup an MySql database and run the scripts in deployment/mysql-scripts in order.

4. Open appsettings.json and update the settings. Refer to the application settings table for details.

5. Run the app with F5

6. Open testing/artillery folder in your terminal (Command Prompt) and run the following commands:

   npm install -g artillery
   artillery run ./MsSqlAutoGenId.yml
   artillery run ./MsSqlGuid.yml
   artillery run ./MsSqlStoredProcedure.yml
   artillery run ./MySqlAutoGenId.yml

Application Settings

Setting	Description
ConnectionStrings.MsSql	Connection string of the MS SQL Database used to store inserted records
ConnectionStrings.MySql	Connection string of the MySQL Database used to store inserted records

Resources

• Transient Error
• Connection Resiliency
• Custom Execution Strategy
• Scaling .Net Core Applications
• Enabling Read Committed Snapshot Isolation in MS SQL server