C# tutorials > Frameworks and Libraries > Entity Framework Core (EF Core) > Performance optimization in EF Core (no-tracking queries, compiled queries, indexing)

Performance optimization in EF Core (no-tracking queries, compiled queries, indexing)

This tutorial explores performance optimization techniques in Entity Framework Core (EF Core), focusing on no-tracking queries, compiled queries, and indexing. By implementing these strategies, you can significantly improve the speed and efficiency of your database interactions.

Introduction to Performance Optimization in EF Core

EF Core offers several techniques to optimize database interactions and improve application performance. These techniques address different aspects of query execution, from reducing overhead to streamlining query compilation. We will cover no-tracking queries, compiled queries, and indexing strategies, which are crucial for building high-performance applications.

No-Tracking Queries: Disabling Change Tracking

No-tracking queries are used when you only need to read data from the database and don't intend to update or delete any entities. By default, EF Core tracks changes to entities retrieved from the database, which adds overhead. The AsNoTracking() method disables this change tracking. This is especially beneficial for read-only operations like displaying data on a webpage or generating reports. 

csharp
using Microsoft.EntityFrameworkCore;
using System.Linq;

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class Example
{
    public void GetBlogsNoTracking()
    {
        using (var context = new BloggingContext())
        {
            var blogs = context.Blogs
                .AsNoTracking()
                .ToList();

            // Blogs are retrieved without change tracking.
        }
    }
}

Concepts Behind No-Tracking Queries

When EF Core tracks entities, it creates a snapshot of the data when it is first retrieved. As properties change, EF Core keeps track of these changes to generate the correct UPDATE statements when SaveChanges() is called. This tracking mechanism consumes memory and CPU cycles. By disabling tracking with AsNoTracking(), we eliminate this overhead, resulting in faster query execution and reduced memory consumption.

Real-Life Use Case for No-Tracking Queries

Imagine displaying a list of products on an e-commerce website. Users primarily browse products; they don't typically modify them directly on the listing page. Using AsNoTracking() when fetching product data for the listing page significantly improves performance, especially when dealing with a large number of products. It reduces the load on the database server and speeds up page load times for users.

Best Practices for No-Tracking Queries

  • Always use AsNoTracking() for read-only scenarios.
  • Avoid using AsNoTracking() when you need to modify entities retrieved from the database. Modifying a non-tracked entity and then trying to update it will result in unexpected behavior.
  • Consider using projection (Select) along with AsNoTracking() to retrieve only the necessary columns. This further reduces the amount of data transferred and processed.

Interview Tip - No-Tracking Queries

When discussing EF Core performance, mentioning no-tracking queries and explaining their benefits demonstrates a solid understanding of optimization techniques. Be prepared to explain when it's appropriate to use them and the potential drawbacks. For instance, "I understand that EF Core, by default, tracks changes to entities retrieved from the database. When retrieving data solely for display or reporting, I use the `AsNoTracking()` method to disable change tracking, improving performance by reducing memory usage and avoiding unnecessary overhead."

When to Use No-Tracking Queries

Use no-tracking queries when:
  • You are only reading data and not modifying it.
  • You are displaying data in a user interface.
  • You are generating reports.
  • You need to improve the performance of read-only operations.

Memory Footprint of No-Tracking Queries

No-tracking queries significantly reduce the memory footprint of your application because EF Core doesn't need to store snapshots of the retrieved entities. This is especially important when dealing with large datasets, as tracking a large number of entities can consume a significant amount of memory and lead to performance degradation.

Compiled Queries: Pre-compiling Query Execution Plans

Compiled queries are pre-compiled LINQ queries that EF Core stores in a cache. This avoids the overhead of repeatedly compiling the same query each time it's executed. Compiled queries are particularly useful for frequently executed queries with static parameters. The EF.CompileQuery method is used to create a compiled query.

csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using System.Linq;
using System; 

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class Example
{
    private static readonly Func<BloggingContext, int, Blog> _compiledQuery =
        EF.CompileQuery((BloggingContext context, int blogId) =>
            context.Blogs
                .FirstOrDefault(b => b.BlogId == blogId));

    public void GetBlogByIdCompiled(int id)
    {
        using (var context = new BloggingContext())
        {
            var blog = _compiledQuery(context, id);

            // Blog retrieved using compiled query.
        }
    }
}

Concepts Behind Compiled Queries

When EF Core executes a LINQ query for the first time, it translates the LINQ expression into a SQL query and then compiles an execution plan for that query. This compilation process takes time. Subsequent executions of the same query require EF Core to recompile the query plan, unless compiled query optimization is used. Compiled queries skip this recompilation step, as the execution plan is already prepared and stored in memory.

Real-Life Use Case for Compiled Queries

Consider an API endpoint that retrieves a user profile based on the user's ID. This endpoint might be called very frequently. By using a compiled query to fetch the user profile, you can significantly reduce the latency of the API endpoint, as the query plan is already compiled and ready to execute. This is especially impactful when handling a large volume of requests.

Alternatives to Compiled Queries (EF Core 7+)

Starting with EF Core 7, query compilation is significantly improved and often obviates the need for explicit compiled queries using `EF.CompileQuery`. EF Core's internal caching mechanism is now much more efficient at recognizing and reusing query plans for similar queries. However, in some niche scenarios or very high-throughput systems, manually compiled queries might still offer a marginal performance benefit. Always benchmark your specific use case to determine if `EF.CompileQuery` is actually providing a measurable improvement.

Best Practices for Compiled Queries

  • Use compiled queries for frequently executed queries with static parameters.
  • Avoid using compiled queries for queries with dynamic parameters. The benefit is minimal and might even degrade performance due to cache pollution.
  • Benchmark your application to measure the actual performance improvement.
  • Consider the maintenance overhead of managing compiled queries.

Interview Tip - Compiled Queries

When discussing compiled queries, be sure to emphasize the importance of benchmarking. Explain that while compiled queries can improve performance, they are not a silver bullet and should only be used after verifying that they provide a measurable benefit. A good response would be: "I know that compiled queries can potentially boost performance by pre-compiling the query plan. However, before implementing them, I would always benchmark to ensure that they offer a real improvement in my specific scenario."

When to Use Compiled Queries

Use compiled queries when:
  • You have a frequently executed query.
  • The query has static parameters.
  • You have benchmarked your application and confirmed that compiled queries improve performance.

Pros of Compiled Queries

  • Reduced query execution time for frequently used queries.
  • Lower CPU usage on the database server.

Cons of Compiled Queries

  • Increased memory consumption (to store compiled query plans).
  • Maintenance overhead (managing compiled queries).
  • Potential for cache pollution if used improperly.

Indexing: Optimizing Data Retrieval

Indexing is a database feature that creates a data structure to speed up data retrieval. Indexes are created on one or more columns in a table. When a query uses a column with an index, the database can quickly locate the relevant rows without scanning the entire table. In EF Core, you can configure indexes using the HasIndex method in the OnModelCreating method.

csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasIndex(b => b.Url);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

Concepts Behind Indexing

Think of an index like the index in a book. Instead of reading the entire book to find a specific topic, you can use the index to quickly locate the relevant pages. Similarly, a database index allows the database engine to quickly locate rows that match a specific search criteria. Without an index, the database has to scan the entire table, which can be very slow for large tables.

Real-Life Use Case for Indexing

Consider a social media platform where users frequently search for posts by keyword. Creating an index on the 'content' column of the 'posts' table significantly improves the speed of these searches. Without an index, the database would have to scan every post to find those that contain the keyword, which would be very slow.

Best Practices for Indexing

  • Create indexes on columns that are frequently used in WHERE clauses.
  • Consider creating composite indexes for queries that use multiple columns in the WHERE clause.
  • Avoid creating too many indexes. Indexes can slow down write operations (INSERT, UPDATE, DELETE) because the database has to update the indexes as well as the table data.
  • Regularly review and optimize your indexes. Unused or poorly designed indexes can degrade performance.

Interview Tip - Indexing

When discussing indexing, highlight the trade-offs between read and write performance. Explain that while indexes improve read performance, they can slow down write operations. A good response would be: "I understand that indexes can significantly improve query performance, but it's important to consider the impact on write operations. I would carefully analyze the query patterns and data modification frequency before creating indexes to ensure a net performance gain."

When to Use Indexing

Use indexing when:
  • You have slow queries that filter data based on specific columns.
  • You need to improve the performance of read-heavy operations.
  • You have analyzed your query patterns and identified columns that are frequently used in WHERE clauses.

Cons of Indexing

  • Slows down write operations (INSERT, UPDATE, DELETE).
  • Increases database storage space.
  • Requires regular maintenance and optimization.

← Moq for mocking Polly for resilience and fault handling (retries, circuit breaker) →

FAQ

  • When should I use `AsNoTracking()`?

    Use AsNoTracking() when you only need to read data from the database and don't intend to update or delete any entities. This is common for displaying data or generating reports.
  • What are compiled queries in EF Core?

    Compiled queries are pre-compiled LINQ queries that EF Core stores in a cache. This avoids the overhead of repeatedly compiling the same query each time it's executed.
  • How do indexes improve performance?

    Indexes speed up data retrieval by creating a data structure that allows the database to quickly locate relevant rows without scanning the entire table.
  • Are Compiled Queries still necessary with EF Core 7 and above?

    With EF Core 7, query compilation is significantly improved and often obviates the need for explicit compiled queries using `EF.CompileQuery`. EF Core's internal caching mechanism is now much more efficient at recognizing and reusing query plans for similar queries. However, in some niche scenarios or very high-throughput systems, manually compiled queries might still offer a marginal performance benefit. Always benchmark your specific use case to determine if `EF.CompileQuery` is actually providing a measurable improvement.