How to Improve API Performance in .Net 7 with EF Core’s Compiled Queries?

 Before starting to explain the process of improving API performance in .Net 7 it's important to learn about some basic terms. So let's get started with compiled queries: 

What are Compiled Queries?

Compiled queries are a technique used in programming and database management to optimize the performance of database queries. The idea behind compiled queries is to convert a database query into a machine code program that can be executed directly by the computer's CPU, rather than interpreting the query each time it is executed. In .Net, Compiled queries are used to compile a LINQ query into a method, which can be reused multiple times. 

Project setup:

First of all, we will create a new web API using ASP.NET (C#). While doing this, you need to make sure that your device has the latest version of Microsoft.EntityFrameworkCore

Creating compiled queries:

Create a class for the person that will represent its name and age:

public class Person {    public int Id { get; set; }    public string Name { get; set; }    public int Age { get; set; } }

In the next step, we will create a DbContext class, and add our Person class as a DbSet. 

public class MyDbContext : DbContext {    public DbSet<Person> Persons { get; set; }    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)    {        optionsBuilder.UseSqlServer("Server=(LocalDb)\\MSSQLLocalDB;Initial Catalog=compiledQueries;Integrated Security=True;");        base.OnConfiguring(optionsBuilder);    } }

Now, we will create two different APIs. One for dynamic queries and one for compiled queries. The APIs are designed to carry out the same task, which is to retrieve a list of all the people in the DB whose age is greater than the passed in age, and returns a sum of all their ages.

app.MapGet("/Dynamic/{age:int}", (int age) => {    using (var db = new MyDbContext())    {        var dynamicQuery = db.Persons.Where(p => p.Age > age).Sum(p => p.Age);        return dynamicQuery;    } });

Here, the first time you call the API might take normal time to execute, but subsequent calls are meant to be significantly faster. This is simply for the same reason explained earlier, the generated SQL is cached and reused across multiple query executions, rather than being dynamically generated each time.

Performance Comparison

Now that we are done setting up the sample project with the differing APIs, let’s test out their execution time.

Using the SQL query below, I have populated my DB with sample data of up to 1000000 records.

CREATE TABLE dbo.Persons (    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,    Name NVARCHAR(50) NOT NULL,    Age INT NOT NULL ); DECLARE @i INT = 1; WHILE @i <= 1000000 BEGIN    INSERT INTO Persons (Name, Age)    VALUES ('Person ' + CAST(@i AS VARCHAR), @i % 100);    SET @i = @i + 1; END

Below is the swagger generated APIs we created in our project.