In high-traffic applications, database performance is critical. Entity Framework Core provides mechanisms like Compiling Queries and Caching to improve query execution times, especially for frequently run queries. This article will demonstrate how to measure performance before and after applying these techniques and how to integrate them into an EventService with examples that can be used in your application’s EventController.

1. Introduction: The Need for Optimization

Without optimizations like Compiling Queries and Caching, EF Core translates and executes queries every time they’re run, leading to unnecessary overhead, especially for frequently run queries. In this article, we’ll:

Compare performance before and after using Compiling Queries.
Apply Caching to prevent redundant database hits.
Implement these optimizations into the EventService class.
Measure performance before and after using these techniques.

2. EventService: Tracking vs. Compiling Queries

First, we’ll demonstrate how to measure performance by updating the EventService to handle both regular and compiled queries.

Before: Regular Query in EventService

Here’s the initial version of EventService that runs a standard query without any optimizations:

public class EventService
{
    private readonly EventDbContext _context;

    public EventService(EventDbContext context)
    {
        _context = context;
    }

    // Regular query to get an event by name
    public async Task<Event> GetEventByNameAsync(string eventName)
    {
        return await _context.Events.FirstOrDefaultAsync(e => e.Name == eventName);
    }
}

After: Compiling Queries in EventService

Now, let's modify the EventService to use compiled queries for frequently executed queries. Compiled queries store the SQL translation of the query and reuse it, eliminating the overhead of translating the query each time.

public class EventService
{
    private readonly EventDbContext _context;

    // Compiled query
    private static readonly Func<EventDbContext, string, Task<Event>> _compiledGetEventByNameQuery =
        EF.CompileAsyncQuery((EventDbContext ctx, string eventName) =>
            ctx.Events.FirstOrDefault(e => e.Name == eventName));

    public EventService(EventDbContext context)
    {
        _context = context;
    }

    // Using compiled query to get an event by name
    public async Task<Event> GetEventByNameCompiledAsync(string eventName)
    {
        return await _compiledGetEventByNameQuery(_context, eventName);
    }
}

3. Measuring Performance: Before and After Compiling Queries

In this section, we'll compare the performance of regular vs. compiled queries.

Regular Query (Before):

   public async Task<IActionResult> GetEventByName(string name)
   {
       var eventItem = await _eventService.GetEventByNameAsync(name);
       return Ok(eventItem);
   }

Compiled Query (After):

   public async Task<IActionResult> GetEventByNameCompiled(string name)
   {
       var eventItem = await _eventService.GetEventByNameCompiledAsync(name);
       return Ok(eventItem);
   }

Benchmarking Performance:

To allow readers to measure performance, you can use a stopwatch to calculate the execution time for both regular and compiled queries:

public async Task<IActionResult> MeasurePerformanceForRegularQuery(string name)
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var eventItem = await _eventService.GetEventByNameAsync(name);

    stopwatch.Stop();
    var executionTime = stopwatch.ElapsedMilliseconds;

    return Ok(new { eventItem, ExecutionTime = executionTime });
}

public async Task<IActionResult> MeasurePerformanceForCompiledQuery(string name)
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var eventItem = await _eventService.GetEventByNameCompiledAsync(name);

    stopwatch.Stop();
    var executionTime = stopwatch.ElapsedMilliseconds;

    return Ok(new { eventItem, ExecutionTime = executionTime });
}

This way, the reader can see the exact time it took to execute both types of queries.

4. Implementing Caching in EventService

Once you’ve optimized queries using compilation, caching can help further by avoiding repeated database hits for frequently requested data.

Before: Regular Query Without Caching

Let’s take a look at the regular GetUpcomingEvents method, which hits the database every time:

public async Task<List<Event>> GetUpcomingEventsAsync()
{
    return await _context.Events
                         .Where(e => e.Date >= DateTime.Now)
                         .ToListAsync();
}

After: Adding Caching to GetUpcomingEvents

To cache this data, we’ll use IMemoryCache to store the result and reuse it for subsequent requests within a certain time window:

public async Task<List<Event>> GetUpcomingEventsWithCachingAsync()
{
    if (!_cache.TryGetValue("upcoming_events", out List<Event> upcomingEvents))
    {
        upcomingEvents = await _context.Events
                                       .Where(e => e.Date >= DateTime.Now)
                                       .ToListAsync();

        _cache.Set("upcoming_events", upcomingEvents, TimeSpan.FromMinutes(10));
    }

    return upcomingEvents;
}

5. Measuring Performance: Before and After Caching

Just like with compiled queries, you can measure the performance of cached queries by comparing execution times with and without caching.

Without Caching (Before):

   public async Task<IActionResult> GetUpcomingEvents(string name)
   {
       var events = await _eventService.GetUpcomingEventsAsync();
       return Ok(events);
   }

With Caching (After):

   public async Task<IActionResult> GetUpcomingEventsWithCaching(string name)
   {
       var events = await _eventService.GetUpcomingEventsWithCachingAsync();
       return Ok(events);
   }

Benchmarking Caching Performance:

Here’s how you can measure the performance of queries with and without caching using a stopwatch:

public async Task<IActionResult> MeasurePerformanceForUpcomingEventsWithoutCaching()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var events = await _eventService.GetUpcomingEventsAsync();

    stopwatch.Stop();
    var executionTime = stopwatch.ElapsedMilliseconds;

    return Ok(new { events, ExecutionTime = executionTime });
}

public async Task<IActionResult> MeasurePerformanceForUpcomingEventsWithCaching()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var events = await _eventService.GetUpcomingEventsWithCachingAsync();

    stopwatch.Stop();
    var executionTime = stopwatch.ElapsedMilliseconds;

    return Ok(new { events, ExecutionTime = executionTime });
}

This approach allows the reader to easily measure performance and compare the benefits of caching in real-time.

6. Applying Best Practices in Production

Best Practices for Compiled Queries

Use compiled queries for frequently executed or complex queries that are likely to benefit from repeated execution.
Avoid overuse of compiled queries in cases where the query logic is dynamic or rarely executed, as the initial compilation overhead might outweigh the benefits.

Best Practices for Caching

Cache expiration: Always set an expiration time to prevent serving stale data.
Invalidate the cache: When data changes (like adding a new event), be sure to invalidate the cache.

Here’s an example of cache invalidation when creating a new event:

public async Task CreateNewEventAsync(Event newEvent)
{
    _context.Events.Add(newEvent);
    await _context.SaveChangesAsync();

    // Invalidate cache
    _cache.Remove("upcoming_events");
}

7. Conclusion

By using Compiled Queries and Caching, you can drastically improve the performance of frequently run queries in EF Core applications.

Compiled Queries eliminate the overhead of query translation, while Caching prevents redundant database hits.
Benchmarking shows that these optimizations can reduce query times by a significant margin.

Make sure to apply these optimizations wisely and follow best practices to ensure optimal performance in production.

Entity Framework Core Tutorial: Compiling Queries and Caching