EF Core 3.0 Cartesian Explosion #8390

AlderCove created 5 years ago

Just a heads up for anyone moving to EF Core 3.0, you might need to refactor some of your queries that use Include for 1:N relations due to performance issues. I first noticed this issue with a query that went from sub second response time to >15s response time.

With EF 2.2, fetching queryable resulted in multiple queries. In EF Core 3.0, a single query is generated, with many joins, which can result in a "Cartesian Explosion".

The solution appears to be to split the original query up into multiple queries and let EF fixup the relations. Tracking must be done to accomplish this, so AsNoTracking() cannot be used with this approach.

Appreciate if the support team has any info/recommendations for handling this scenario that they are able to share.

Jamie

According to MS: https://docs.microsoft.com/en-us/ef/core/querying/related-data Caution

Since version 3.0.0, each Include will cause an additional JOIN to be added to SQL queries produced by relational providers, whereas previous versions generated additional SQL queries. This can significantly change the performance of your queries, for better or worse. In particular, LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries in order to avoid the cartesian explosion problem.

Additional references:

Significant Query Slowdown When Using Multiple Joins Due To Changes In 3.0 #18022 https://github.com/dotnet/efcore/issues/18022

20x slowdown in gigantic query after updating to EF Core 3 compared to 2.2 https://github.com/dotnet/efcore/issues/18017

EF Core 3.0 .Include does not work as expected and Super Slow https://stackoverflow.com/questions/58677119/ef-core-3-0-include-does-not-work-as-expected-and-super-slow

4 Answer(s)