Entity Framework 6.2 database first warm up

Question

I have an EF 6.2 project in my MVC solution.

This uses a SQL server db and has about 40 tables with many foreign keys.

The first query is very slow, 20 secs.

I immediately hit the same page again, changing the user param, and the query takes less than 1 second.

So this looks like a warm up issue in EF6. That's fine and there's loads of things i can do to sort apparently.

The Model cache (part of EF6.2) looks like it could be beneficial, but everywhere i read about it states model first. Nothing about DB first. Would this still work with db first?
Also there's the Entity Framework 6 power tools, these allow for me to Generate Views. Tried this and it doesn't seem to make any difference. Is this still a valid route?
Any other ideas?

Is the query an SP that the server needs to compile (maybe with the recompile option)? Is it just the first time or always with a particular value? The speed up might be the database retrieving the data from disk, the following might be fast if the contents are already cached. Is it really the database or it might be another MVC resource? — EzLo, Feb 13 '19 at 13:45
it's Entity framework, no SPs used at all. Always the first time after a publish or if the site has been quite for a while. — Alex Stephens, Feb 13 '19 at 13:48
Have you peeked at https://stackoverflow.com/questions/13250679/how-to-warm-up-entity-framework-when-does-it-get-cold — EzLo, Feb 13 '19 at 13:52
thanks, yes i've read that but it doesn't answer the main 2 questions i asked. — Alex Stephens, Feb 13 '19 at 14:06
I'm wondering how complex is the query? Does it have many includes? I find that taking out the includes from the initial query and then calling them separately afterwards improved performance significantly especially after app restart. — Steven Manuel, Feb 14 '19 at 04:47

score 2 · Answer 1 · answered Feb 14 '19 at 03:50

EF DbContexts incur a one-off cost to resolve their entity mappings. For web applications you can mitigate this by having your application start up kick off a simple query against the DbContext which "kicks off" this warm-up rather than during your first user-triggered query. Simply new-ing up a context doesn't trigger the initialization, running a query does. So for ASP.Net MVC on the Application_Start, after initializing everything:

using (var context = new MyContext())
{
   var warmup = context.MyTable.Count(); // against a small table.
}

You can test this behaviour with unit tests by having a suite of timed tests that read data from the DbContext, and putting a break-point in DbContext's OnModelCreating event. It will be executed just once from the first test with the first query. You can add a OneTimeSetUp in a test fixture setup to run ahead of the tests with the above quick count example to incur this cost before measuring the performance of the test runs.

score 0 · Accepted Answer · answered Feb 25 '19 at 08:55

So, the answer was to update EF to 6.2 then use the newest feature:

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration() : base()
    {
        var path = Path.GetDirectoryName(this.GetType().Assembly.Location);
        SetModelStore(new DefaultDbModelStore(path));
    }
}

for the full story check out this link: https://entityframework.net/why-first-query-slow

Your gonna take a small performance hit at startup but then it all moves a lot faster.

For anyone using an Azure web app you can use a deployment slot (https://stackify.com/azure-deployment-slots/), this allow you to publish into a non-production slot then warm it up before swapping it in as the production slot.

Entity Framework 6.2 database first warm up

2 Answers2