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:

pg
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.

Newsletter Anmeldung
Diese Artikel könnten Sie interessieren
Database Access with Sessions
.NET
KP-round

Data Access in .NET Native AOT with Sessions

.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023
Old computer with native code
.NET
KP-round

Native AOT with ASP.NET Core – Overview

Originally introduced in .NET 7, Native AOT can be used with ASP.NET Core in the upcoming .NET 8 release. In this post, we look at the benefits and drawbacks from a general perspective and perform measurements to quantify the improvements on different platforms.
02.11.2023
.NET
KP-round

Optimize ASP.NET Core memory with DATAS

.NET 8 introduces a new Garbage Collector feature called DATAS for Server GC mode - let's make some benchmarks and check how it fits into the big picture.
09.10.2023
.NET CORE
pg

Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

With the version 4.3.1 of Microsoft.CodeAnalysis.* Roslyn provides a new high-level API - the method "ForAttributeWithMetadataName". Although it is just 1 method, still, it addresses one of the biggest performance issue with Source Generators.
16.05.2023
.NET
sg

.NET 7 Performance: Regular Expressions – Part 2

There is this popular quote by Jamie Zawinski: Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."

In this second article of our short performance series, we want to look at the latter one of those problems.
25.04.2023
.NET
sg

.NET 7 Performance: Introduction and Runtime Optimizations – Part 1

.NET 7 is fast. Superfast. All the teams at Microsoft working on .NET are keen to improve the performance and do so every year with each new .NET release. Though this time the achievements are really impressive. In this series of short articles, we want to explore some of the most significant performance updates in .NET and look at how that may affect our own projects. This first article is taking a deep look under the hood of the compiler and the runtime to look for some remarkably interesting and significant updates.
28.03.2023