Linq to Entities 性能和预生成视图 [英] Linq to Entities performance and pregenerating views

问题描述

首次运行 linq to EF 查询需要很长时间.在预先生成视图后，我很惊讶地发现没有任何区别.我在 stackoverflow

We are taking a long time on first runs of our linq to EF queries. I was surprised to see no difference after pre-generating views. I ran across the following claim on stackoverflow

视图生成仅对标准"查询有帮助(例如，当您调用 someObject.RelatedEnd.Load() 或 MyContext.SomeSetName() 时.由于显而易见的原因，它对使用 LINQ 或 ESQL 的即席查询没有帮助.使用 CompiledQuery 来优化这些.

View generation only helps for "standard" queries (e.g., when you call someObject.RelatedEnd.Load() or MyContext.SomeSetName(). It doesn't help for ad hoc queries with LINQ or ESQL, for obvious reasons. Use CompiledQuery to optimize those.

当他说出于显而易见的原因"时，我不得不说，嗯，对我来说还不是很明显".如果我理解正确，他声称 Linq to SQL 查询不受预生成 EF 视图的影响.

When he says "for obvious reasons" I have to say, "Well,not obvious to me yet". If I understand this correctly he is claiming Linq to SQL queries are not affected by pregenerating EF views.

我曾认为实体视图是实体和表之间的通用映射，与任何特定查询无关.这是误会了吗?

I had thought entity views were generic mappings between entities and tables, and bore no relation to any specific query. Is this mistaken?

我可以看到在我们的 Linq to Entities 查询的第一次运行中使用了大量时间，之后的时间大大缩短，因此我假设正在为相关实体和表生成视图.如果它不是一个可以在第一次运行时预先生成的 EF 视图，那么它是什么?

I can see huge amounts of time being used in the first run of our Linq to Entities queries, with dramatically smaller times thereafter, so I assumed views were being generated for the relevant entities and tables. If it isn't an EF view that can be pregenerated taking all that first run time, then what is it?

所以我的问题分为三个部分:

So my question has three parts:

1. 是否为每个查询生成 EF 视图，或者它们只是将表与实体相关联，而不管进行的查询如何?

1. Are EF views generated for each query, or do they just relate tables to entities regardless of the queries made?

上述关于预生成 EF 视图对 Linq to EF 查询没有影响的说法是否正确?是否必须改用 CompileQueries?

Is the above claim that pregenerating EF views makes no difference in Linq to EF queries correct? Does one have to use CompileQueries instead?

注意:我什至不会问，但如果您使用的是 EF，互联网上有很多建议(包括 msdn)来预生成视图.这是我看到的唯一一个建议，如果您使用 Linq to Entities，那么预生成与您的查询无关.

Note: I wouldn't even ask but there are quite a few recommendations on the internet (including msdn) to pregenerate views if you are using EF. This is the only place I have seen it suggested that if you use Linq to Entities then pregenerating is irrelevant to your queries.

推荐答案

我不认为你找到的答案是正确的.您可以将 EF 视图视为一种抽象数据库的方式，以便 EF 可以在不了解实际数据库的情况下完成其工作.因此，EF 需要通过 EF 查询/更新管道进行的任何查询或修改操作的视图.事实上，任何/所有 EF 查询(无论是 Linq 查询还是 ESQL 查询)都是通过组合实际上是视图的基本查询来构建的.

I don't think the answer you found is correct. You can think of EF views as of a way of abstracting the database so that EF can do its stuff without knowing anything about the actual database. Therefore EF requires views for any query or modification operation that goes through the EF query/update pipeline. In fact, any/all EF queries (be it Linq queries or ESQL queries) are built by composing on base queries which are actually the views.

回答你的问题:

• 通常在第一次查询时仅生成一次视图
• 编译查询和视图是正交的 - 我在上面解释了视图，编译查询是关于缓存生成的 SQL 以进行查询以避免再次编译查询.这很有帮助，因为通常在应用程序中使用的一组查询是有限的，因此可以缓存生成的 SQL(请注意，如果将参数传递给查询，这不会影响生成的 SQL，因为它也会被参数化).从 EF5 开始，缓存会自动发生 (http://blogs.msdn.com/b/efdesign/archive/2011/06/30/auto-compiled-linq-queries-entity-framework-june-2011-ctp.aspx).

在 EF6 中，对视图生成进行了许多改进.我会说，在绝大多数情况下，您根本不需要使用 EF6 的预生成视图(我以 用于为 EF5 生成视图的 T4 模板和 EF6 以及 EF6 的交互式视图).但是，我们发现对于 EF6 中的 Code First 应用程序，实际瓶颈在于构建模型.还有一些其他性能问题 - 请参阅 这篇博文了解更多详情.EF6.0.2 中修复了很多这些问题，因此如果您尚未升级到 EF6.0.2，则应该升级.我认为 EF 6.1 中还有更多与性能相关的修复.

In EF6 there have been a number of improvements to view generation. I would say that in the vast majority of cases you shouldn't have to use pre-generated views with EF6 at all (and I say this as the author of the T4 templates for generating views for EF5 and EF6 and also interactive views for EF6). However we found that for Code First apps in EF6 the actual bottleneck was building the model. There were a few other perf issues as well - see this blog post for more details. A lot of these issues were fixed in EF6.0.2 so if you have not upgraded to EF6.0.2 you should. I think there is a few more perf related fixes coming in EF 6.1.

旁注:注意他说的是 LINQ 或 ESQL 而不是 Linq to SQL.ESQL 是 EF 支持的类似 SQL 的查询语言 - 如果您有兴趣可以阅读更多 这里.由于 EF 对 LINQ 有很好的支持，因此实际上并没有很多场景需要使用 ESQL 而不是 Linq to Entities.Linq to SQL 与 EF/ESQL/Linq to Entities 无关.

Side note: Note he said LINQ or ESQL and not Linq to SQL. ESQL is a SQL-like query language supported by EF - if you are interested you can read more here. Since EF has a good support of LINQ there is not really a lot of scenarios where you would want to use ESQL rather than Linq to Entities. Linq to SQL is unrelated to EF/ESQL/Linq to Entities.

这篇关于Linq to Entities 性能和预生成视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！