Entity framework very slow to load for first time after every compilation

Question

As the title suggest i'm having a problem with the first query against a SQL Server database using the Entity Framework. I have tried looking for an answer but no one seems to actually have a solution to this.

The tests was done in Visual Studio 2012 using Entity Framework 6, I also used the T4 views template to pre-compile the views. The database was on a SQL Server 2008. We have about 400 POCOs (400 mapping files), only have 100 rows data in database table.

Following capture is my test code and result.

static void Main(string[] args){
    Stopwatch st=new Stopwatch();
    st.Start();
    new TestDbContext().Set<Table1>.FirstOrDefault();
    st.stop();
    Console.WriteLine("First Time "+st.ElapsedMilliseconds+ " milliseconds");

    st.Reset();
    st.Start();
    new TestDbContext().Set<Table1>.FirstOrDefault();
    st.stop();
    Console.WriteLine("Second Time "+st.ElapsedMilliseconds+ " milliseconds");
}

Test results

First Time 15480 milliseconds
Second Time 10 milliseconds

Answer 1

On the first query EF compiles the model. This can take some serious time for a model this large.

Here are 3 suggestions: http://www.fusonic.net/en/blog/2014/07/09/three-steps-for-fast-entityframework-6.1-first-query-performance/

A summary:

1. Using a cached db model store
2. Generate pre-compiled views
3. Generate pre-compiled version of entityframework using n-gen to avoid jitting

I would also make sure that I compile the application in release mode when doing the benchmarks.

Another solution is to look at splitting the DBContext. 400 entities is a lot and it should be nicer to work with smaller chunks. I haven't tried it but I assume it would be possible to build the models one by one meaning no single load takes 15s. See this post by Julie Lerman https://msdn.microsoft.com/en-us/magazine/jj883952.aspx

Answer 2

With EF Core, you can cheat and load the model early after you call services.AddDbContext (you can probably do something similar with EF6 too, but I haven't tested it).

services.AddDbContext<MyDbContext>(options => ...);
var options = services.BuildServiceProvider()
                      .GetRequiredService<DbContextOptions<MyDbContext>>();
Task.Run(() =>
{
    using(var dbContext = new MyDbContext(options))
    {
        var model = dbContext.Model; //force the model creation
    }
});

This will create the model of the dbcontext in another thread while the rest of the initialization of the application is done (and maybe other warmups) and the beginning of a request. This way, it will be ready sooner. When you need it, EFCore will wait for the Model to be created if it hasn't finished already. The Model is shared across all DbContext instances so it is ok to fire and forget this dummy dbcontext.

Answer 3

You can try something like this: (it worked for me)

protected void Application_Start()
{

    Start(() =>
    {
        using (EF.DMEntities context = new EF.DMEntities())
        {
            context.DMUsers.FirstOrDefault();
        }
    });
}
private void Start(Action a)
{
    a.BeginInvoke(null, null);
} 

Entity Framework - First query slow

Answer 4

this work for me:

using (MyEntities db = new MyEntities())                
{
   db.Configuration.AutoDetectChangesEnabled = false; // <----- trick
   db.Configuration.LazyLoadingEnabled = false; // <----- trick

   DateTime Created = DateTime.Now;

   var obj = from tbl in db.MyTable
      where DateTime.Compare(tbl.Created, Created) == 0
      select tbl;

   dataGrid1.ItemsSource = obj.ToList();
   dataGrid.Items.Refresh();
}

Answer 5

If you have many tables that are not being used on c#, exclude them.

Add a partial class, add the following code and reference this function on OnModelCreating

void ExcludedTables(DbModelBuilder modelBuilder)
{
    modelBuilder.Ignore<Table1>();
    modelBuilder.Ignore<Table>();
   // And so on
}

Answer 6

For me, just using AsParallel() in the first query solved the problem. This runs the query on multiple processor cores (apparently). All my subsequent queries are unchanged, it is only the first one which was causing the delay.

I also tried pre-generated mapping views https://learn.microsoft.com/en-us/ef/ef6/fundamentals/performance/pre-generated-views but this did not improve startup time by much.

Answer 7

I think that is not a very good solution. Ado.net looks like a lot more performance. However, this is my opinion.

Alternatively look at them.

https://msdn.microsoft.com/tr-tr/data/dn582034

https://msdn.microsoft.com/en-us/library/cc853327(v=vs.100).aspx