Entity Framework Core 7 Performance: Cartesian Explosion

In Entity Framework Core 3 (EF 3) the SQL statement generation (re)introduced the Cartesian Explosion problem. A lot has happened since then, so it is time to revisit the issue with Entity Framework Core 7 (EF 7).

In this article:

pg
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.

What is a "Cartesian Explosion"?

As the name implies, it has something to do with a cartesian product, i.e., with JOINs. When performing a JOIN on the one-to-many relationship then the rows on the one-side are replicated N times whereby N is the number of matching records on the many-side.

Please note: In this article, I use synchronous methods for better readability.
Also note: In this article, we are talking about one-to-many/many-to-many relationships only. A one-to-one relationship does not cause the Cartesian Explosion.

Here is an example for JOIN-ing 1 ProductGroup with 1000 Products.
The corresponding LINQ query:

				
					var groups = Context.ProductGroups
          .Include(g => g.Products)
          .ToList();
				
			

The SQL statement is like the following one:

				
					SELECT *
FROM [ProductGroups] AS [p]
LEFT JOIN [Products] AS [p0]
   ON [p].[Id] = [p0].[ProductGroupId]
ORDER BY [p].[Id]

				
			

And the result set:

ProductGroup Id
Product Id
1
1
1
2
1
3
1
1
1000

The columns of the ProductGroup are replicated 1000 times.

If there were another navigational property Sellers and there were 10 sellers per Product, then the result set will contain 1 * 1000 * 10 = 10000 rows, although we have just 1 + 1000 + 10 = 1011 records in the database.

				
					var groups = Context.ProductGroups
          .Include(g => g.Products)
          .ThenInclude(p => p.Sellers)
          .ToList();
				
			

With a few Include/ThenInclude more, the result set (i.e., the cartesian product) will explode.

EF-Forced "ORDER BY"

The larger result set due to JOINs is not the only performance issue. Depending on the included navigational properties, EF might add one or more ORDER BY clauses.

The good news is EF 7 doesn’t add an additional ORDER BY clause anymore when 1 navigational property is included; in contrast, EF 3 orders the data by both product group ID and product ID.

				
					SELECT *
FROM [ProductGroups] AS [p]
LEFT JOIN [Products] AS [p0]
   ON [p].[Id] = [p0].[ProductGroupId]
ORDER BY [p].[Id] // EF 7
// ORDER BY [p].[Id], [p0].[Id] // EF 3
				
			

The performance hit comes back when 2 or more navigational properties are included, e.g., Sellers.

				
					var groups = Context.ProductGroups
          .Include(g => g.Products)
          .ThenInclude(p => p.Sellers)
          .ToList();
				
			

For better materialization, i.e., the population of .NET entities, EF puts all identifiers into the ORDER BY clause. The resulting query will produce a considerable load on the database if the result set gets big.

				
					SELECT *
FROM [ProductGroups] AS [p]
LEFT JOIN (
    SELECT *
    FROM [Products] AS [p0]
    LEFT JOIN (
        SELECT *
        FROM [SellerProducts] AS [s]
        INNER JOIN [Sellers] AS [s0] ON [s].[SellerId] = [s0].[Id]
    ) AS [t] ON [p0].[Id] = [t].[ProductId]
) AS [t0] ON [p].[Id] = [t0].[ProductGroupId]
ORDER BY [p].[Id], [t0].[Id], [t0].[ProductId], [t0].[SellerId]

				
			

Query Splitting

The solution to the Cartesian Explosion Problem in EF 7 is still the same as in EF 3. The LINQ query should be split into multiple SQL statements if (and only if) the database load rises significantly. This can be done either manually, by writing multiple LINQ queries, or by using the method AsSplitQuery.

Please note, that the “alternative” way produces not the same result as the original query. The query Context.Sellers.ToList() loads all sellers and not just the ones attached to a product. Loading all sellers is an additional optimization because we know that (almost) all of them are going to be returned anyway.

For the following comparisons, the database contains 1000 products, 10 product groups, and 2 sellers.

				
					var groups = Context.ProductGroups
       // .AsSplitQuery()
          .Include(g => g.Products)
          .ThenInclude(p => p.Sellers)
          .ToList();

// Alternative to "AsSplitQuery"
var groups = Context.ProductGroups.ToList();
var products = Context.Products.ToList();
var sellers = Context.Sellers.ToList();
				
			

Below are some database statistics (MS SQL Server). The absolute numbers are not relevant. Just look at the relative difference, especially in Reads and Rows.

With query splitting, the CPU time seems to be less than 1ms, so the statistics return 0ms. I put 1ms instead because 0ms looks very odd.

Without AsSplitQuery
With AsSplitQuery
Alternative Way
CPU
16
1
1
Duration
233
420
264
Reads
6123
36
16
Rows
2000
2010
1012

With query splitting, the duration gets up because there are multiple round-trips to the database. But this increase in duration is more than acceptable if the number of reads goes down from 6k to 36 or even 16.

Summary

The Cartesian Explosion is still an issue in EF 7 and it is very likely not to disappear in the newer versions. EF team gave us the method AsSplitQuery which can mitigate the problem. This tool yields good results, but it won’t be a replacement for manual optimization by the developers.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

EN Newsletter Anmeldung (#7)
Related Articles
AI
sg
One of the more pragmatic ways to get going on the current AI hype, and to get some value out of it, is by leveraging semantic search. This is, in itself, a relatively simple concept: You have a bunch of documents and want to find the correct one based on a given query. The semantic part now allows you to find the correct document based on the meaning of its contents, in contrast to simply finding words or parts of words in it like we usually do with lexical search. In our last projects, we gathered some experience with search bots, and with this article, I'd love to share our insights with you.
17.05.2024
Angular
SL-rund
If you previously wanted to integrate view transitions into your Angular application, this was only possible in a very cumbersome way that needed a lot of detailed knowledge about Angular internals. Now, Angular 17 introduced a feature to integrate the View Transition API with the router. In this two-part series, we will look at how to leverage the feature for route transitions and how we could use it for single-page animations.
15.04.2024
.NET
KP-round
.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023