Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Performance of Compiled Queries in Entity Framework Core 2.0

DZone's Guide to

Performance of Compiled Queries in Entity Framework Core 2.0

Compiled queries are a new feature of Entity Framework Core 2.0, but how do they perform? Let's take a look.

· Performance Zone ·
Free Resource

xMatters delivers integration-driven collaboration that relays data between systems, while engaging the right people to proactively resolve issues. Read the Monitoring in a Connected Enterprise whitepaper and learn about 3 tools for resolving incidents quickly.

Before applying any optimizations to our code we have to ask one question: what is the cost of improvement and is it really improvement? Compiled queries in Entity Framework 2.0 are categorized as high-availability features, but before making any decisions we need to know what is the actual win. This blog post introduces the measurements I made with simple database context to compare compiled and uncompiled queries in Entity Framework Core 2.0.

Test Database Context

I’m using simple database context with in-memory provider to have minimum overhead. This way I also avoid using real database that makes it harder to understand what part of performance raise came from code and what came by database optimizing execution plans and caching data. Database context knows only one entity type called Category.


public class Category
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Category Parent { get; set; }
}

Additionally, it has compiled query to get first top-level category and methods to get top-level category with and without compiled query. Here is my database context.


public class TestDbContext : DbContext
{
    private static Func<TestDbContext, Category> _getCategoryCompiled =
            EF.CompileQuery((TestDbContext ctx) =>
                ctx.Categories.Include(c => c.Parent)
                                    .Where(c => c.Parent == null)
                                    .OrderBy(c => c.Name)
                                    .FirstOrDefault());

    public TestDbContext(DbContextOptions<TestDbContext> options) : base(options)
    {    
    }       

    public DbSet<Category> Categories { get; set; }

    public void FillCategories()
    {
        var foodCategory = new Category { Id = Guid.NewGuid(), Name = "Food", Parent = null };           Categories.AddRange(
            foodCategory,
            new Category { Id = Guid.NewGuid(), Name = "Drinks", Parent = null },
            new Category { Id = Guid.NewGuid(), Name = "Clothing", Parent = null },
            new Category { Id = Guid.NewGuid(), Name = "Electronics", Parent = null }
        );           for(var i = 0; i < 50; i++)
        {
            Categories.Add(new Category { Id = Guid.NewGuid(), Name = "Random", Parent = foodCategory });
        }           SaveChanges(true);
    }       

    public Category GetTopLevelCategory()
    {
        return Categories.Include(c => c.Parent)
                            .Where(c => c.Parent == null)
                            .OrderBy(c => c.Name)
                            .FirstOrDefault();
    }       

    public Category GetTopLevelCategoryCompiled()
    {
        return _getCategoryCompiled(this);
    }
}

With this dummy database context I made simple measurements.

Measurement Methodics

I am sorry I did no scientific level measuring but went the easy way. I decided to run enough cycles to bring out differences we can sense and understand. I saw some results in Christos Matskas's blog post Improving EF Core performance with Compiled Queries, but there compiled queries didn't make much difference as loops were actually pretty short. I decided to go with much longer loops – 100K cycles to see the difference. And I can tell – there is big difference.

I made three different measurements:

  1. Compiling query
  2. Querying for top level category
  3. Querying for top level category with compiled query

I ran all these measurements in loops with length of 100K cycles and I ran measurements multiple times. Every time I got almost the same results.

Using Stopwatch class I measured how much time it takes for each of these three measurements to run 100K times.

Compiling the Query

First I ran query compiling to see what the cost of this action is. Using a small number of cycles we don’t see any difference. But 100K cycles was different.


private static void MeasureCompiling()
{
    var watch = new Stopwatch();
    var cycles = 100000;       
    watch.Start();
    for (var i = 0; i < cycles; i++)
    {
        EF.CompileQuery((TestDbContext ctx) =>
                ctx.Categories.Include(c => c.Parent)
                                    .Where(c => c.Parent == null)
                                    .OrderBy(c => c.Name)
                                    .FirstOrDefault());
    }
    watch.Stop();       
    Console.Write("Compiling: ");
    Console.WriteLine(watch.Elapsed);
}

The result on my machine is 6 sec in average.

Measuring Uncompiled Query

I did next 100K rounds with GetTopLevelCategory() method of test database context. This method queries the data source and every time the query is compiled again.


private static void MeasureUncompiledQuery()
{
    var options = new DbContextOptionsBuilder<TestDbContext>()
                            .UseInMemoryDatabase(Guid.NewGuid().ToString())
                            .Options;
    var context = new TestDbContext(options);
    var watch = new Stopwatch();
    var cycles = 100000;       
    context.FillCategories();
    watch.Start();       

    for (var i = 0; i < cycles; i++)
    {
        context.GetTopLevelCategory();
    }
    watch.Stop();       

    Console.Write("Uncompiled query: ");
    Console.WriteLine(watch.Elapsed);
}

100K queries on my machine took ~34 sec in average.

Measuring Compiled Query

It’s time to see if compiled query makes any difference or not. For this I ran GetTopLevelCategoryCompiled() method of test database context 100K times.


private static void MeasureCompiledQuery()
{
    var options = new DbContextOptionsBuilder<TestDbContext>()
                            .UseInMemoryDatabase(Guid.NewGuid().ToString())
                            .Options;
    var context = new TestDbContext(options);
    var watch = new Stopwatch();
    var cycles = 100000;       

    context.FillCategories();
    watch.Start();                  

    for (var i = 0; i < cycles; i++)
    {
        var top = context.GetTopLevelCategoryPrecompiled();
    }
    watch.Stop();       

    Console.Write("Compiled query: ");
    Console.WriteLine(watch.Elapsed);
}

100K rounds with compiled query on my machine gave way better results: ~17 sec.

I was able to get the same results on all runs of measurements. There were only small fluctuations (±800ms) but not something big like 5 seconds or something like this.

Entity Framework Core: Preformance of compiled queries

Simple calculation shows that we can achieve up to 50% in performance with compiled queries.

NB! Don’t take these numbers as absolute truth. The actual performance gain may be totally different—higher or lower—depending on concrete application and database. The numbers given here originate from one simple set of tests and these don't give any insights about overall effect on system when using compiled queries.

Wrapping Up

When just running a few methods in test application we don’t notice any difference between compiled and uncompiled queries. But running those queries in loop 100K times was enough on my machine to bring out better the differences in running times. After these experiments we probably understand why compiled queries are part of high-availability section in Entity Framework Core documentation. Although compiled queries are a powerful way to avoid repeating work in an application when it runs, we cannot yet make all queries use it. Currently all queries returning more than one result (IEnumerable<T>, IQueryable<T>) are not supported. Hopefully these queries will be supported in future versions of Entity Framework Core.

3 Steps to Monitoring in a Connected Enterprise. Check out xMatters.

Topics:
entity framework core ,database ,performance ,queries ,.net

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}