Entity Framework Code First Pre-Compiled Views Not Speeding Up First Call

Question

I used EF PowerTools (EF5) in VS2012 to generate pre-compiled views for my large code-first DataContext. Unfortunately, this didn't help speed up the first call to the data context. It still takes about 13 seconds. Are there some considerations for using pre-compiled views that I'm not taking into account? I am not using migrations and I'm disabling database initialization.

<connectionStrings>
    <add name="MyDataContext" connectionString="..." providerName="System.Data.SqlClient" />
</connectionStrings>

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <contexts>
        <context type="MyNameSpace.MyDataContext, MyNameSpaceAssembly" disableDatabaseInitialization="true" />
    </contexts>
</entityFramework>

score 0 · Accepted Answer · edited May 23 '17 at 10:33

After digging through the Internet with various search terms, I was able to figure this out. There is a problem using precompiled views if your entities are not in the same assembly as the data context. The issue is described here:

Which assembly should I place the compiled views for entity framework code first when context is in a separate project from the domain classes

There is a workaround, however, I find it to be quite a hack. The first DbSet entity defined in the context must reside in the same assembly as the DataContext. I created this arbitrary entity class in my context's assembly:

public class PreCompiledView
{
    public int PreCompiledViewId { get; set; }
}

and added the DbSet to my context:

public class MyDataContext : DbContext
{
    #region DBSets

    // HACK: Enable pre-compiled views
    internal DbSet<PreCompiledView> PreCompiledViews { get; set; }

    // My entity sets
    public DbSet<MyOtherAssemblyEntity> MyOtherAssemblyEntities { get; set; }
    ...

    #endregion
}

My pre-compiled views are now reflected and used by the datacontext. In my custom database initializer Seed() override, I execute a SQL DROP statement to drop the dbo.PrecompiledViews table in order to keep it hidden from developers.

context.Database.ExecuteSqlCommand("DROP TABLE [dbo].[PreCompiledViews]");

score -1 · Answer 2 · answered Nov 24 '14 at 18:03

-1

They are two separate issues. Precompiling the views doesn't create the execution plan. The execution plan is what is taking up most of the time on the first request. Precompiling the views helps but it is not what takes up the majority of the time.

answered Nov 24 '14 at 18:03

Honorable Chow

3,097
3
22
22

Well, I think there is more going on. First, the time with and without the views is pretty much the same. Second, I just realized that I forgot to recompile the views upon recently adding a new entity property. In the project referencing my EF assembly, I was actually able to load the entity with the new property, set it and save successfully. Should this even have been possible if the pre-compiled view didn't get generated with the new property/column? – Jason Butera Nov 24 '14 at 21:21
no, you should have gotten an exception when you ran the application and you had not recompiled the views after changing the model. – Honorable Chow Nov 24 '14 at 21:26
Yes, that's what I thought. My troubleshooting seems to indicate that the views are not being used at all. I'm trying to track down the reason. I right-clicked my context, selected Entity Framework => Generate Views. I didn't know if there was more to it. – Jason Butera Nov 24 '14 at 21:33
I'm using a test project in the same solution in debug. I put a break point in the GetViewAt() method of the generated view file and it's never hit. So confused. – Jason Butera Nov 24 '14 at 22:34
@HonorableChow - in EF5 generating views was really slow. Like 20 **minutes** slow. Preparing queries could also be slow but not as slow. Note that sending any query or command using EF requires views so in the case of the first query it was sometimes really bad. The improvements in EF6 made view generation up 100 times faster in some scenarios so in EF6 you rarely need to pre-generate views. – Pawel Nov 25 '14 at 05:26

Entity Framework Code First Pre-Compiled Views Not Speeding Up First Call

2 Answers2