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.