Related Topic: Entity framework performance tuning

Does Entity Framework Core always generate query with best performance? Does any body have a Counter-Example (EF query has less performance vs raw SQL query (EF generate a SQL query that we can write it in better way using raw SQL))?

Thanks

CodePudding user response：

OK, one case maybe really stands out. It is that EF always generates outer joins when Include()-ing a collection. If we have prior knowledge that parents without children don't exist (or we don't need them) there's no way to instruct EF to generate an inner join for the Include, while there may be considerable performance differences between outer (worst) and inner joins (best).

To me, this was annoying enough to make me coin an IncludeInner method, which was rejected, unfortunately (and, fair enough, I didn't submit a pull request).

From an EF perspective it makes sense to generate outer joins. After all, a query like...

context.Parents.Include(p => p.Children)

...is expected to return all parents, not only the ones with children.

So now if we're interested in only parents with children we have to revert to a query like:

context.Parents.Include(p => p.Children)
    .Where(p => p.Children.Any()

We now have an OUTER JOIN and an EXISTS predicate that both access the Children table. If performance is really critical we can't but write SQL with an inner join ourselves, or write LINQ with a join statement, not using navigation proeprties.

In EF's defense, it should be noted that query generation in general has improved immensely in EF-core, as compared to its early versions and esp. Entity Framework 6 (for .net framework). While EF6 was lamentable, I dare say that EF-core-6 is one of the best now. (Although GroupBy support remains an issue).

CodePudding user response：

There are many performance tips for Efficient Querying like Tracking, no-tracking and identity resolution, split queries , indexes ,
EF is ORM (object relational mapping) that finally translated to end Db Engine To prevent the programmer from getting involved in the database language has its own overhead cost you can also use raw SQL queries with FromSqlRaw

you can learn more

https://docs.microsoft.com/en-us/ef/core/performance/

CodePudding user response：

Short answer is No,

Explanation

EFCore (short for Entity Framework Core) does not aim to generate query with better performance, EFCore only aims to generate query based on the model you have provided. EFCore will create correct query based on the result you are trying to achieve.

Queries are only generated based on the navigation properties and foreign keys involved.

Databases mostly use index statistics to generate best query plans, that has nothing to do with if query is written using EFCore or plain SQL.

Irrespective of the way you write queries using EFCore, SQL server will choose most efficient plan always.

Split Queries

EFCore provides query splitting, which results in smaller datasets compared to many number of joins in single query generated by older Entity Framework.

You can read about it here: https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries, it definitely is faster compared to earlier Entity Framework.

We have seen query times dropping to 20ms from 2 seconds, using split queries. There are very small inconsistencies as both occur in different time, and are actually executed as many separate queries. But for most part, it is sufficient and fast. And best part is you can use .AsSingleQuery() to use single query.