如何减少Entity Framework 4查询编译时间？ [英] How to reduce Entity Framework 4 query compile time?

问题描述

总结：我们遇到EF4查询编译时间超过12秒的问题。缓存的查询只会让我们到目前为止;有什么办法可以减少编译时间吗？有什么我们可能在做错吗，我们可以寻找？谢谢！

我们有一个通过WCF服务公开的EF4模型。对于我们的每个实体类型，我们公开了一种方法来获取并返回整个实体进行显示/编辑，包括一些引用的子对象。

对于一个特定实体，我们有到.Include（）31个表/子表返回所有相关数据。不幸的是，这使得EF查询编译过于缓慢：编译和建立一个7,800行的300K查询需要12-15秒。这是一个Web UI的后端，需要比这更快。

有什么可以改进的吗？我们可以CompiledQuery.Compile这个 - 在第一次使用之前不会做任何工作，所以有助于第二次和后续的执行，但是我们的客户紧张，第一次使用也不应该很慢。类似地，如果托管Web服务的IIS应用程序池被回收，我们将失去缓存的计划，尽管我们可以增加寿命以最小化这一点。我也看不到预先编译的方法，和/或串行化EF编译查询缓存（缺少反思技巧）。 CompiledQuery对象只包含缓存中的GUID引用，因此它是我们真正关心的缓存。 （写这个出来，我发现我可以从app_startup的背景中启动一些东西来执行所有的查询以使它们被编译 - 是否安全？）

然而即使我们解决这个问题，我们使用LINQ到Entities子句动态构建我们的搜索查询，基于我们正在搜索的参数：我不认为SQL生成器做得足够好，可以将所有逻辑进入SQL层，所以我不认为我们可以预编译我们的搜索查询。这不太严重，因为搜索数据结果使用较少的表，所以只有3-4秒编译不是12-15，但客户认为终端用户仍然不会接受。

所以我们真的需要减少查询编译时间。任何想法？

• 分析点指向ELinqQueryState.GetExecutionPlan作为开始的地方，我试图进入，但没有真正的.NET 4源可用我不能得到很远，反射器产生的源不会让我进入某些功能或设置断点。


• 该项目从.NET 3.5升级，所以我尝试从EF4重新生成EDMX，以防万一出现问题，但没有帮助。


• 我已经尝试过在这里公布的EFProf实用程序，但它看起来不像这样会有所帮助。我的大型查询也破坏了数据收集器。


• 我已经通过SQL性能调优运行生成的查询，它已经具有100％的索引使用率。我看不到数据库出现任何问题，导致查询生成器出现问题。


• 执行计划编译器中有一些O（n ^ 2）是将其分解成块单独的数据加载而不是所有32个表一次可能有帮助？将EF设置为延迟加载没有帮助。


• 我已经买了预发行的O'Reilly Julie Lerman EF4书，但是在编译你的查询中找不到任何帮助。

我不明白为什么要花费12-15秒才能在32个表格中生成一个单一的选择，所以我很乐观改进的范围！

感谢任何建议！我们正在运行SQL Server 2008，如果重要，XP / 7 / server 2008 R2使用RTM VS2010。

解决方案

你的查询更简单认真;查询复杂度与编译时间之间几乎呈线性关系。两个简单的查询通常比一个真正复杂的查询快得多（即使是预编译的）。如果速度是最终目标，选择最快的选项。

Summary: We're having problems with EF4 query compilation times of 12+ seconds. Cached queries will only get us so far; are there any ways we can actually reduce the compilation time? Is there anything we might be doing wrong we can look for? Thanks!

We have an EF4 model which is exposed over the WCF services. For each of our entity types we expose a method to fetch and return the whole entity for display / edit including a number of referenced child objects.

For one particular entity we have to .Include() 31 tables / sub-tables to return all relevant data. Unfortunately this makes the EF query compilation prohibitively slow: it takes 12-15 seconds to compile and builds a 7,800-line, 300K query. This is the back-end of a web UI which will need to be snappier than that.

Is there anything we can do to improve this? We can CompiledQuery.Compile this - that doesn't do any work until first use and so helps the second and subsequent executions but our customer is nervous that the first usage shouldn't be slow either. Similarly if the IIS app pool hosting the web service gets recycled we'll lose the cached plan, although we can increase lifetimes to minimise this. Also I can't see a way to precompile this ahead of time and / or to serialise out the EF compiled query cache (short of reflection tricks). The CompiledQuery object only contains a GUID reference into the cache so it's the cache we really care about. (Writing this out it occurs to me I can kick off something in the background from app_startup to execute all queries to get them compiled - is that safe?)

However even if we do solve that problem, we build up our search queries dynamically with LINQ-to-Entities clauses based on which parameters we're searching on: I don't think the SQL generator does a good enough job that we can move all that logic into the SQL layer so I don't think we can pre-compile our search queries. This is less serious because the search data results use fewer tables and so it's only 3-4 seconds compile not 12-15 but the customer thinks that still won't really be acceptable to end-users.

So we really need to reduce the query compilation time somehow. Any ideas?

• Profiling points to ELinqQueryState.GetExecutionPlan as the place to start and I have attempted to step into that but without the real .NET 4 source available I couldn't get very far, and the source generated by Reflector won't let me step into some functions or set breakpoints in them.
• The project was upgraded from .NET 3.5 so I have tried regenerating the EDMX from scratch in EF4 in case there was something wrong with it but that didn't help.
• I have tried the EFProf utility advertised here but it doesn't look like it would help with this. My large query crashes its data collector anyway.
• I have run the generated query through SQL performance tuning and it already has 100% index usage. I can't see anything wrong with the database that would cause the query generator problems.
• Is there something O(n^2) in the execution plan compiler - is breaking this down into blocks of separate data loads rather than all 32 tables at once likely to help? Setting EF to lazy-load didn't help.
• I've bought the pre-release O'Reilly Julie Lerman EF4 book but I can't find anything in there to help beyond 'compile your queries'.

I don't understand why it's taking 12-15 seconds to generate a single select across 32 tables so I'm optimistic there's some scope for improvement!

Thanks for any suggestions! We're running against SQL Server 2008 in case that matters and XP / 7 / server 2008 R2 using RTM VS2010.

解决方案

Make your queries simpler. Seriously; there's an almost linear relationship between query complexity and compile time. Two simple queries are often much faster than one really complicated query (even if precompiled!). If speed is the end goal, choose the fastest option.

这篇关于如何减少Entity Framework 4查询编译时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！