Initial performance issue with LINQ to Entities when using TPH & complex types, Pregen Views seem to do nothing?

Question

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

I am creating csdl, edml, msl and ssdl files for the Model in a seperate Model Project.

These files are produced by Entity developer from Devart, where I manage my entity model.

Target: Entity Framework 5

• Lazy Loading : Enabled
• View Generation : True
• Validation on build : True
• Metadata Artifact Processing: Embed in Output Assembly
• ObjectContext is used
• ObjectContext Proxy Creation Enabled : true.

I have implemented TPH inheritance in my entity model, whereby the child class is also made up of upto 10 complex types(CTn).

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

Each complex type maps to column in the generic Animal Table.

My initial LINQ is:

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

When this is run for the first time, it can take around 40 secs to complete. Successive runs take about 200ms.

When I analyse this further using an ORM Profiler, it gives me the LINQ code as:

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

I discovered a wonderful SO question at: Related SO Question, but it does not go deep enough. While it recommends an upgrade to EF6, it does not mention the new EF6 issue of having to JIT the EF runtime on first use due to it being external to the .NET runtime now. Perhaps in a later version of EF6 ie 6.1.2 this is solved.

Once the T-SQL is created by EF, it runs, in its own right, very quickly. I have tested this in SSMS.

So my question, as of November 2014, is made up of:

1) How can I resolve initial load delays for my TPH/Complex Type scenario, having tried pregenerated Views. I have seen references to "Compiled Queries".

2) Perhaps I should upgrade to EF6? However if I do, is there now a JIT penalty for the EF runtime itself, and how should I resolve this. I deploy into Azure Websites.

3) Finally I spotted that other more straightforward queries benefited from the pregenerated views, so I am using them. It is just for this TPH/Complex Type scenerario it has no impact. Are there situations where pregen views have no impact?

4) Could 3) be due to the time taken to "autocompile a query" which EF5 can now do. Having pregenerated the View, I guess this is the next bottleneck. Perhaps this "autocompile" feature for complex entities like mine takes a long time, so could one do a proactive manual compile? Quess this is what one calls a "CompiledQuery". Is it relevant to write this extra code, or would EF6x help me here? I have a strong hunch that this query compilation step is the bottleneck, but also realise that writing compiled queries is also not necessarily the most easy and maintainable solution. At present we have a periodic startup job that just warms up all of these complex entities, so the user goes straight into "warm query execution mode".

Any help with the above would be hugely appreciated.

EDIT1

I have just used JetBrains's DotTrace Profile which goes much deeper, and it seems to confirm that my bottleneck is happening with :

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

On first hit, it spends 99% of the time here, which confirms my view that it was something to do with the Query Plan generation. How I resolve this issue is another matter.

EDIT2

I am going to test out on EF 6.1.2 after some good advice and having followed Julie Lerman's excellent course on Pluralsight.

Answer 1

I had a similar problem and tried multiple approaches to improve 'first hit' performance.

Have you thought about implementing IProcessHostPreloadClient to pre-warm your cache and making an initial call, via your Entity Framework model, to your database?

This solution worked for me and I've 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 hook this up to your AutoStart provider on IIS.