Summary: We are having problems compiling EF4 requests in 12+ seconds. Cached requests will still get us; are there any ways to reduce compilation time? Is there something we can do wrong we can look for? Thanks!
We have an EF4 model that maps through WCF services. For each of our entity types, we set a method to retrieve and return the entire entity for display / editing, including the number of referenced children.
For one specific object, we need .Include () 31 tables / sub-tables to return all the relevant data. Unfortunately, this makes compiling EF requests prohibitively slow: it takes 12-15 seconds to compile and build a request with a length of 7,800 lines, 300 KB. This is an internal web interface that should be more successful than that.
Is there anything you can do to improve this? We can CompiledQuery.Compile this - it does not do any work before the first use, and therefore helps in the second and subsequent executions, but our client is nervous that the first use should not slow down either. Likewise, if the IIS application pool hosting the web service is redesigned, we will lose the caching plan, although we can increase the lifetime to minimize this. In addition, I see no way to precompile this ahead of time and / or serialize from the cache of compiled EF requests (without reflection tricks) from the cache. The CompiledQuery object contains only a GUID link to the cache, so we need a cache. (Having written this, it occurs to me that I can start something in the background from app_startup to execute all the requests in order to compile them - is it safe?)
However, even if we solve this problem, we dynamically create our search queries using LINQ-to-Entities clauses based on what parameters we are looking for: I don’t think the SQL generator does a good enough job, we can move all this logic into the SQL layer, so I don’t think we can precompile our search queries. This is less serious because the data search results use fewer tables, and therefore it is only 3-4 seconds, not 12-15, but the client believes that it still will not be acceptable to end users.
So we really need to somehow reduce the query compilation time. Any ideas?
- Profiling points to ELinqQueryState.GetExecutionPlan as a place to start, and I tried to get into it, but without a real .NET 4 source I could not get very far, and the source created by Reflector would not let me go to some functions or set control functions in them points.
- The project was updated with .NET 3.5, so I tried to restore EDMX from scratch in EF4 if something was wrong with it, but that did not help.
- I tried the EFProf utility advertised here, but it doesn't seem to help. In any case, my big request resets its data collector.
- I executed the generated query using SQL performance tuning, and it already uses 100% of the index. I do not see anything wrong with the database, which can cause problems with the query generator.
- Is there something O (n ^ 2) in the execution plan compiler - splits it into blocks of individual data, and not into all 32 tables that can help? Setting EF to lazy load did not help.
- I bought a preview of O'Reilly Julie Lerman EF4, but I can’t find anything there to help you “compile your queries”.
I don’t understand why it takes 12-15 seconds to create a single selection across 32 tables, so I am optimistic there for improvement!
Thanks for any suggestions! We work with SQL Server 2008 in case it is important, and XP / 7 / server 2008 R2 using RTM VS2010.