The Performance Issue “Cartesian Explosion” Made Its Comeback In Entity Framework Core 3

In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced another issue: the Cartesian Explosion Problem.

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 implied by the name, 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 of the one-side are being replicated N times whereby N is the number of matching records on the many-side.

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

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

The SQL statement is similar to the following one:

				
					SELECT *
FROM ProductGroups
LEFT JOIN
    Products
    ON Products.GroupId = ProductGroups.Id
				
			

And the result set:

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

As we see, the columns of the ProductGroup are replicated 1000 times. Imagine there are 10 sellers per Product – the result set will contain 1 * 1000 * 10 = 10000 rows although we have just 1 + 1000 + 10 = 1011 records in the database.

I should be clear what happens if we add a few Includes more. The result set (i.e. the cartesian product) would explode.

EF-forced "ORDER BY"

The larger result set due to JOINs is not the only cause for lower performance. Let’s look at the SQL statement generated by EF. Btw, the SQL statement above is not complete but the following one is:

				
					SELECT 
    [p].[Id], [p].[Name], [p].[RowVersion],
    [p0].[Id], [p0].[GroupId], [p0].[Name], [p0].[RowVersion]
FROM
    [ProductGroups] AS [p]
LEFT JOIN 
    [Products] AS [p0] 
    ON [p].[Id] = [p0].[GroupId]
ORDER BY
    [p].[Id], [p0].[Id]

				
			

For internal purposes, the EF adds an ORDER BY clause to order the entities by their identifiers. So, with a result set of that huge size, the ordering of this data will produce considerable load on the database.

Query splitting (back to the roots)

The solution of the Cartesian Explosion Problem that came with Entity Framework Core 3 is the same as with Entity Framework (non-Core) 6. We split 1 LINQ query in multiple queries if (and only if) the database load rises significantly.

When using our (oversimplified) example from above then the solution is to load Products and ProductGroups separately.

				
					var groups = Context.ProductGroups.ToList();
var products = Context.Products.ToList();
				
			

Here are some database statistics (MS SQL Server) I get when loading data having two one-to-many relationships before and after query splitting. The absolute numbers are not relevant, just look at the relative difference, especially in the Reads and Rows.

Before splitting
After splitting
CPU
31
16
Duration
75
3
Reads
5300
350
Rows
12000
2300

Summary

In this blog article, I wanted to convey two things: there is a new (old) issue we have to be aware of, and this issue can be solved.
The difficulty is finding such queries and determining how to split them. If we split too much, we waste time. If we split too little, we waste performance. The tools I highly recommend using for this task are the database statistics and execution plans.

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_300x300
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_300x300
.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