The initial problem with LINQ to Entities when using TPH and complex types, does Pregen views seem to do nothing?

Question

I am using MVC3, ASP.NET 4.5, LINQ for Entities, EF5 and SQL Server 2008 R2 and Azure (for live).

I am creating csdl, edml, msl and ssdl files for a model in a separate model project.

These files are created by Devity's Entity developer, where I manage my entity.

Purpose: entity infrastructure 5

• Lazy Loading: Enabled
• View Generation: True
• Assembly Verification: True
• Metadata artifact processing: built into output assembly
• Used by ObjectContext
• ObjectContext Proxy Creation Enabled: true.

I implemented TPH inheritance in my entity model, in which the child class also consists of up to 10 complex types (CTn).

Animal<-Cat(CT1,CT2,CT3 etc) (for example) 

Each complex type is mapped to a column in a common animal table.

My starting LINQ:

 if (db.Animal.OfType<Cat>().Any(a => a.OwnerId == myOwnerId)) 

When this is done for the first time, it may take about 40 seconds to complete. Successive runs take about 200 ms.

When I analyze this using ORM Profiler, it gives me LINQ code like:

 Cat.MergeAs(0).Any(a => a.OwnerId == ((Nullable<int>)myOwnerId)) 

I found a wonderful SO question: A related question , but it does not go deep enough. Although he recommends upgrading to EF6, it does not mention the new EF6 issue related to the need to start the EF startup environment when it is first used, because it is external to the .NET runtime. Perhaps in a later version of EF6, i.e. 6.1.2, this is resolved.

Once T-SQL is created by EF, it runs on its own very quickly. I checked this in SSMS.

So my question, starting in November 2014, consists of:

1) How can I resolve the initial load delays for my TPH / Complex Type script by trying pregenerated Views. I saw links to Compiled Queries.

2) Maybe I need to upgrade to EF6? However, if I do this, is there currently a JIT penalty for the EF runtime environment itself and how do I resolve it. I host Azure sites.

3) Finally, I noticed that other simpler queries benefited from the regenerated views, so I use them. It is for this TPH / Complex Type scenario that it does not affect. Are there situations where the preferences of pregents are not affected?

4) Maybe 3) because of the time taken to “autocompile the request,” which EF5 can now do. Anticipating the view, I think this is the next bottleneck. Perhaps this “auto-compile” function for complex objects like mine takes a lot of time, so you can do proactive manual compilation? Think this is what is called "CompiledQuery". Does it make sense to write this extra code, or will EF6x help me here? I have a strong hunch that this stage of query compilation is a bottleneck, but also understand that writing compiled queries is also not necessarily the easiest and most convenient solution. Currently, we have periodic launch work that only warms up all these complex objects, so the user goes directly to the "warm query execution mode".

Any help with the above would be greatly appreciated.

EDIT1

I just used the JetBrains DotTrace profile, which is much deeper, and seems to confirm that my bottleneck is happening with:

 System.Data.Query.PlanCompiler.PreProcessor.Process(Dictionary[EdmFunctionEdmProperty[]]&) 

On the first hit, he spends 99% of the time here, which confirms my opinion that this is somehow related to the generation of the Query Plan. How I solve this problem is another matter.

EDIT2

I am going to check out EF 6.1.2 after some good tips and have followed Julie Lerman with an excellent course on Pluralsight.

Answer 1

I had a similar problem and tried to use several approaches to improve the performance of the “first strike”.

Have you ever thought about implementing IProcessHostPreloadClient to pre-heat your cache and create an initial call through your Entity Framework model to your database?

This solution worked for me, and I created a sample PreWarmCache class below:

 namespace MyNamespace { public class PreWarmCache : IProcessHostPreloadClient { private static readonly NLog.Logger Logger = NLog.LogManager.GetCurrentClassLogger(); private readonly SystemRepository _systemRepository = new SystemRepository(); public void Preload(string[] parameters) { // Perform initialization and cache loading logic Logger.Debug("Application Started: {0}", _systemRepository.InitAppliation() ? "DB response OK" : "DB response failed"); } } } 

Check out this blog for details on how to connect this to the AutoStart provider in IIS.