Compiled queries under Entity Framework as a performance enhancement

#microsoft #coding #dotnet #development

Most of the applications we create today are database-driven. Here, efficient query performance is an important aspect. One powerful option EF offers here is compiled queries. But what are they exactly? And how do they work?

Compiled queries
If the same queries are used repeatedly within an application, e.g. to create lists or load individual values from the database, compiled queries can achieve a significant increase in performance here. Compiled queries are compiled “once” and are then available to the application; they do not have to be repeatedly recreated and evaluated. This leads to a significant performance increase of up to 15% compared to conventional queries. Shouldn’t that be reason enough to investigate this topic?

Create a compiled query
In the example I created, we will now look at the simple data model:

For this, I have created a database that is created and populated in memory. Here I use the nuget of “Microsoft.EntityFrameworkCore.InMemory”.

The Data Model:
This is a employee table that has 5 properties and is configured via FluentAPI.



public class Employee
{
  public int Id { get; set; }
  public string FirstName { get; set; }=string.Empty;
  public string LastName { get; set; }= string.Empty;
  public string Department { get; set; } = string.Empty;
  public int EntryYear { get; set; } 
}
public class DataTypeConiguration : IEntityTypeConfiguration<Employee>
{
  public void Configure(EntityTypeBuilder<Employee> builder)
  {
    builder.HasKey(c => c.Id);
    builder.Property(c => c.Id).ValueGeneratedOnAdd();
  }
}

When the context is created, random values are generated in these tables. I will not go into more detail here to focus on the compiled query.

The table could look like this, for example, after the values have been generated at runtime:

We now want to query and display the entire list. I have realised this via a service that receives these queries and executes them via the context:



public IEnumerable<Employee> GetAllData() 
{
   var listOfData = _context.Data.ToList();
   return listOfData;
}

For this, the compiler needs about 196ns and uses an allocation of 92kb.

If we want to convert exactly this query into a compiled query, we would have to proceed as follows:

First, we store the query in a private static field and use the EF.CompileQueries function to compile the query via the DBContext and then submit it at the appropriate time:



private static readonly Func<DBContext, IEnumerable<Employee>> DataList = 
  EF.CompileQuery((DBContext db) => db.Data.ToList());
public IEnumerable<Employee> GetAllData_Compiled()
{
   return DataList(_context).ToList();
}

The static field is created when the application program is started and is used as soon as the function GetAllData_Compiled() is called. This increases the performance considerably.

For this, the compiler needs about 170ns and uses an allocation of 85kb.

Increasing of 13%

!! Important!!!

The function EF.CompileQuery only uses optimisations that handle the part of the queries that is executed in memory. The materialisation and round-trip time of the results from the database remain unaffected.

How does this increase come about?

This optimisation works quite simply. An Entity Framework LINQ statement needs to be converted into a valid SQL statement at runtime, which happens with traditional queries every time they are executed. After the query is executed, the application forgets about this statement.

The EF.CompileQuery function, on the other hand, keeps this query in memory and only executes it when it is needed. It can only do this because it is a static class:



public static partial class EF

...
This saves processing time and leads to the described performance improvements.

Compiled queries asynchronously
Of course, these compiled queries can also be executed asynchronously:

To do this, the EF.CompiledAsyncQuery must be included in the static field instead of the EF.CompiledQuery function.



private static Func<DBContext, IAsyncEnumerable<Employee>> DataListAsync = 
EF.CompileAsyncQuery((DBContext ctx) => ctx.Data);

here, no further asynchronous function is needed within the query, as this is converted internally.
The result is then processed within the calling function and returned:



public async Task<IEnumerable<Employee>> GetAllData_Compiled_Async()
{
  var result = new List<Employee>();

  await foreach (var s in DataListAsync(_context))
  {
    result.Add(s);
  }

  return result;
}

!! Small tip !!!

We can still improve the performance a little by deactivating the instructions of which we know exactly that we do not need the change tracking (e.g. Just ReadOnly results)!



public async Task<IEnumerable<Employee>> GetAllData_Compiled_Async()
{
    _context.ChangeTracker.QueryTrackingBehavior = 
    QueryTrackingBehavior.NoTracking;

   var result = new List<Employee>();

   await foreach (var s in DataListAsync(_context))
   {
      result.Add(s);
   }

   return result;
 }

Sometimes it can also be helpful to precompile queries if they are to return single values.

From this query you would use “normally”,



public Employee? GetDataById(int id)
{
    var data = _context.Data.FirstOrDefault(x=>x.Id == id);
    return data;
}

Would then this compiled query from it:



private static readonly Func<DBContext, int,  Employee?> DataById = 
  EF.CompileQuery((DBContext db, int id) => db.Data.FirstOrDefault(x => x.Id == id));

public Employee? GetDataById_Compiled(int id)
{
    return DataById(_context, id);
}

Asynchronously, this would be called as follows:



private static Func<DBContext, int, Task<Employee?>> DataById_Async = 
  EF.CompileAsyncQuery((DBContext ctx, int id) => ctx.Data.FirstOrDefault(x=>x.Id==id));
public async Task<Employee?> GetDataById_Compiled_Async(int id)
{
    return await DataById_Async(_context, id);
}

It should be noted that no asynchronous query is executed within the query on the context (FirstOrDefault). This is internally converted from this synchronous query to an asynchronous query.

Even though compiled queries increase the complexity of the code, they are a powerful tool that can be used wisely. If used wisely, they can lead to a significant increase in performance and contribute to the smooth running of the application.