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 this article:

pg
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.

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.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

EN Newsletter Anmeldung (#7)
Related Articles
AI
sg
One of the more pragmatic ways to get going on the current AI hype, and to get some value out of it, is by leveraging semantic search. This is, in itself, a relatively simple concept: You have a bunch of documents and want to find the correct one based on a given query. The semantic part now allows you to find the correct document based on the meaning of its contents, in contrast to simply finding words or parts of words in it like we usually do with lexical search. In our last projects, we gathered some experience with search bots, and with this article, I'd love to share our insights with you.
17.05.2024
Angular
sl_300x300
If you previously wanted to integrate view transitions into your Angular application, this was only possible in a very cumbersome way that needed a lot of detailed knowledge about Angular internals. Now, Angular 17 introduced a feature to integrate the View Transition API with the router. In this two-part series, we will look at how to leverage the feature for route transitions and how we could use it for single-page animations.
15.04.2024
.NET
kp_300x300
.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