Entity Framework Core 5 Performance: Power Of Table Hints

A few months ago, I had to implement a Web API for a relatively simple use case, but there was a challenge. The web endpoint must read and update a specific record using Entity Framework Core 5 and be capable of handling thousands of requests per second. Another requirement was the *data integrity*, so a transaction was a must. With high concurrency and Microsoft SQL Server as the database, we can end up in a deadlock if the SQL Server locks the records or rather the pages in an *unfavorable* way. Let's see how *table hints* can help us.

In diesem Artikel:

Entity Framework Core 5 Performance: Power Of Table Hints
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

Sample Application

First, we will create a sample application for the reproduction of the issues and for measuring the throughput. For that, we create an ASP.NET Core application with one endpoint and a DbContext with one entity, Product.

Demo DbContext

The entity Product has two properties: an ID and a DateTime, which will be changed on every call of the web API.

				
					public class Product
{
   public Guid Id { get; set; }
   public DateTime LastProductAccess { get; set; }
}

public class DemoDbContext : DbContext
{
   public DbSet<Product> Products { get; set; }

   public DemoDbContext(DbContextOptions<DemoDbContext> options)
      : base(options)
   {
   }
}
				
			

Later, during the startup of the application, we will create one Product with a hard-coded identifier. This ID will be used for load tests.

				
					var id = new Guid("06D6E029-320B-4347-9E47-03CCFF153652");

if (!await dbContext.Products.AnyAsync(p => p.Id == id))
{
   dbContext.Products.Add(new() { Id = id, LastProductAccess = DateTime.UtcNow });
   await dbContext.SaveChangesAsync();
}
				
			

Demo Controller

 

The ASP.NET controller has one action, which reads a Product with the provided ID and updates the property LastProductAccess.

				
					[Route("[controller]")]
public class DemoController : Controller
{
   private const IsolationLevel _ISOLATION_LEVEL =
         IsolationLevel.ReadCommitted // Violates our business requirements,
                                      // but just for testing purposes
      // IsolationLevel.RepeatableRead
      // IsolationLevel.Serializable
      ;

   private readonly DemoDbContext _ctx;

   public DemoController(DemoDbContext ctx)
   {
      _ctx = ctx;
   }

   [HttpPost("{id}")]
   public async Task DoAsync(Guid id)
   {
      await using var tx = await _ctx.Database.BeginTransactionAsync(_ISOLATION_LEVEL);

      var product = await _ctx.Products.FirstAsync(p => p.Id == id);

      // do more or less complex stuff

      product.LastProductAccess = DateTime.UtcNow;

      await _ctx.SaveChangesAsync();

      await tx.CommitAsync();
   }
}
				
			

Choosing the Transaction Isolation Level

For consistency, a transaction is the first that comes to mind. The question is, what isolation level should we choose, and what are the implications?

Isolation Level: ReadCommitted

The isolation level ReadCommitted is usually the default one. Although it is not sufficient for our purposes because it allows others to modify the data we are currently working on, still, it is interesting to see how it will perform.

I’m using hey for the execution of multiple concurrent requests. We execute 10k requests with a concurrency level of 50.

After the execution of the load tests, we see that all requests have responded with the status code 200, and we are able to handle 5k requests per second.

Please note: the absolute values are not important and will vary from machine to machine. What matters are the differences between measurements.

				
					> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        1.9819 secs
  Slowest:      0.0303 secs
  Fastest:      0.0052 secs
  Average:      0.0099 secs
  Requests/sec: 5045.5772

Status code distribution:
  [200] 10000 responses
				
			

The numbers are more than acceptable for our use case, but the isolation level is not.

Isolation Level: RepeatableRead

The first isolation level that suffices our requirements is RepeatableRead. We change the isolation level in the controller and rerun the load tests.

				
					[Route("[controller]")]
public class DemoController : Controller
{
   private const IsolationLevel _ISOLATION_LEVEL =
      // IsolationLevel.ReadCommitted // Violates our business requirements,
                                      // but just for testing purposes
         IsolationLevel.RepeatableRead
      // IsolationLevel.Serializable
      ;
				
			

Shortly after the start of the load test, our application drowns in exceptions due to deadlocks.

				
					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.
  --> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating 
      the entries. See the inner exception for details.
     --> Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 102)
         was deadlocked on lock resources with another process and has been chosen as the
         deadlock victim. Rerun the transaction.
				
			

Most of the requests ended up with the status code 500, and the performance turned out very bad. Sure, we could try to catch the exception and retry or activate EnableRetryOnFailure() as suggested in the error message, but it will make everything worse.

				
					> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        56.0751 secs
  Slowest:      8.3635 secs
  Fastest:      0.0957 secs
  Average:      0.2801 secs
  Requests/sec: 178.3322

Status code distribution:
  [200] 400 responses
  [500] 9600 responses
				
			

Isolation Level: Serializable

The isolation level Serializable is the strictest one, so it may help us to prevent the deadlocks – but it did not. We still get as many deadlocks as with RepeatableRead.

				
					> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        55.2665 secs
  Slowest:      7.5805 secs
  Fastest:      0.0133 secs
  Average:      0.2756 secs
  Requests/sec: 180.9414

Status code distribution:
  [200] 401 responses
  [500] 9599 responses

				
			

Consequently, switching the isolation level alone won’t help to improve the throughput unless we are willing to sacrifice the data integrity.

Table Hints

The table hints aren’t supported by Entity Framework by default, i.e., we have to implement this feature ourselves or use a 3rd party library. In this article, I use the library Thinktecture.EntityFrameworkCore.SqlServer.

We change the LINQ query so that the Product is loaded with a UpdLock and RowLock, and retry the load test.

				
					var product = await _ctx.Products
                        .WithTableHints(SqlServerTableHint.UpdLock,
                                        SqlServerTableHint.RowLock)
                        .FirstAsync(p => p.Id == id);

				
			

The output of the load test shows that the application is stable now; we have no deadlocks anymore. The throughput is not as high as with IsolationLevel.ReadCommitted and without table hints, but it is the worst-case scenario we have here. In production, we won’t be hammering on the one and only record over and over again but use different records.

				
					> hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        4.6708 secs
  Slowest:      0.0446 secs
  Fastest:      0.0160 secs
  Average:      0.0233 secs
  Requests/sec: 2140.9711

Status code distribution:
  [200] 10000 responses
				
			

Let’s see whether we can improve the performance a bit more.

Bonus: Compiled Queries

In this section, we will replace the only LINQ query with a compiled query.

				
					[Route("[controller]")]
public class DemoController : Controller
{
   // definition and "caching" of the compiled query
   private static readonly Func<DemoDbContext, Guid, Task<Product>> _loadProductAsync
      = EF.CompileAsyncQuery((DemoDbContext ctx, Guid id) 
               => ctx.Products
                     .WithTableHints(SqlServerTableHint.UpdLock, SqlServerTableHint.RowLock)
                     .First(p => p.Id == id));

    ...
    
   [HttpPost("{id}")]
   public async Task DoAsync(Guid id)
   {
      await using var tx = await _ctx.Database.BeginTransactionAsync(_ISOLATION_LEVEL);

      // replacement of the LINQ-query with compiled query
      var product = await _loadProductAsync(_ctx, id);

      // do more or less complex stuff

      product.LastProductAccess = DateTime.UtcNow;

      await _ctx.SaveChangesAsync();

      await tx.CommitAsync();
   }
				
			

The results are not bad. We almost got 500 requests per second more, for free.

				
					>hey -n 10000 -c 50 -m POST http://localhost:5000/demo/06D6E029-320B-4347-9E47-03CCFF153652

Summary:
  Total:        3.8418 secs
  Slowest:      0.0295 secs
  Fastest:      0.0091 secs
  Average:      0.0192 secs
  Requests/sec: 2602.9580

Status code distribution:
  [200] 10000 responses
				
			

Summary

The table hints may have been forgotten when working with Entity Framework. Still, they shouldn’t be underestimated when it comes to performance and stability of the application.

Kostenloser
Newsletter

Aktuelle Artikel, Screencasts, Webinare und Interviews unserer Experten für Sie

Verpassen Sie keine Inhalte zu Angular, .NET Core, Blazor, Azure und Kubernetes und melden Sie sich zu unserem kostenlosen monatlichen Dev-Newsletter an.

Diese Artikel könnten Sie interessieren
.NET
Incremental Roslyn Source Generators in .NET 6: Adapt Code Generation Based on Project Dependencies – Part 5

Incremental Roslyn Source Generators in .NET 6: Adapt Code Generation Based on Project Dependencies – Part 5

The Roslyn Source Generator, implemented in the previous articles of the series, emits some C# code without looking at the dependencies of the current .NET (Core) project. In this article our DemoSourceGenerator should implement a JsonConverter, but only if the corresponding library (e.g. Newtonsoft.Json) is referenced by the project.
08.07.2022
Unterschiede
.NET
Blazor WebAssembly vs. Blazor Server – Welche Unterschiede gibt es und wann wähle ich was?

Blazor WebAssembly vs. Blazor Server – Welche Unterschiede gibt es und wann wähle ich was?

Das Blazor Framework von Microsoft gibt es inzwischen in drei "Geschmacksrichtungen". Die erste ist Blazor WebAssembly, die zweite Blazor Server, und zu guter Letzt gibt es noch Blazor Hybrid. In diesem Artikel wollen wir uns die zwei "echten", also Browser-basierten, Web-Anwendungs-Szenarien WebAssembly und Server anschauen.
04.07.2022
Three different textured walls
.NET
Dependency Injection Scopes in Blazor

Dependency Injection Scopes in Blazor

The dependency injection system is a big part of how modern ASP.NET Core works internally: It provides a flexible solution for developers to structure their projects, decouple their dependencies, and control the lifetimes of the components within an application. In Blazor - a new part of ASP.NET Core - however, the DI system feels a bit odd, and things seem to work a bit differently than expected. This article will explain why this is not only a feeling but indeed the case in the first place and how to handle the differences in order to not run into problems later on.
31.05.2022
.NET
Incremental Roslyn Source Generators in .NET 6: Increasing Performance through Harnessing of the Memoization – Part 4

Incremental Roslyn Source Generators in .NET 6: Increasing Performance through Harnessing of the Memoization – Part 4

In Part 1 of this series we've implemented a simple Incremental Source Generator. Although we looked at all mandatory phases of an IIncrementalGenerator, still, the consideration was quite superficial. One of the biggest advantages of the new API, which makes the Source Generator an incremental one, is the built-in memoization, i.e. caching. To take full advantage from it, we have to make some preparations so Roslyn knows what to cache and how to compare the results of each phase.
10.05.2022
.NET
Intro: Blazor WebAssembly für Angular-Entwickler – Teil 1 [Screencast]

Intro: Blazor WebAssembly für Angular-Entwickler – Teil 1 [Screencast]

Jahrelang gab es kein Framework von Microsoft, mit dem es möglich war, webbasierte Cross Plattform Applikationen zu erstellen. Und dann kam Blazor WebAssembly. Im ersten Teil dieser Screencast-Serie wird erklärt, worum es sich bei Blazor WebAssembly handelt.
05.05.2022
Blazor
[Sneak Preview] Blazor WebAssembly: The Power Of EF Core And SQLite In The Browser – In-Depth

[Sneak Preview] Blazor WebAssembly: The Power Of EF Core And SQLite In The Browser – In-Depth

Rumor has it, this is the dream of many .NET developers: Using SQLite and EF Core in the browser with Blazor WebAssembly. Is this possible? In this article, we will have a look how you can manage your offline data with the help of SQLite and EF Core by persisting the data with standard browser features and APIs.
12.04.2022