CODEWITHSUNDEEP
entity-frameworkentity-framework-6
user334911
user334911

Reputation:

Linq to Entities performance and pregenerating views

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

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.

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?

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. Are EF views generated for each query, or do they just relate tables to entities regardless of the queries made?

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

  3. What are the "obvious reasons" referred to above?

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.

Upvotes: 1

Views: 897

Answers (1)

Pawel
Pawel

Reputation: 31610

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.

To answer your questions:

  • Views are generated only once typically on the first query
  • compiled queries and views are orthogonal - I explained views above, compiled queries are about caching the generated SQL for a query to avoid compiling the query again. This helps because typically a set of queries used in an app is limited so the generated SQL can be cached (note that if you pass parameters to the query this does not affect the generated SQL since it will be parameterized as well). Starting from EF5 the caching happens automatically (http://blogs.msdn.com/b/efdesign/archive/2011/06/30/auto-compiled-linq-queries-entity-framework-june-2011-ctp.aspx).

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.

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.

Upvotes: 1

Related Questions