Understanding cartesian explosion

Question

When reading about cartesian explosion almost all of the articles are related to EF core. The recommended way to avoid the cartesian explosion is by splitting queries (which also causes problems). https://docs.microsoft.com/en-us/ef/core/performance/efficient-querying#avoid-cartesian-explosion-when-loading-related-entities

Wouldn't it be possible to solve the problems with cartesian explosion by writing a proper query using FromSqlRaw? Since the majority of the articles are about EF core/LINQ it feels like this could be avoided when writing raw sql, or is there some other property in the ORM that is causing the issue?

Answer 1

When you write db-related code, you should somehow predict how the results will look like.

In my case, most queries contains 1-3 joins and it's usually faster (in terms of performance) to execute it using eager-loading (so, there'll be only one round-trip to the database). Anyway to imagine you the "dangers" of eager loading, check the example:

To simplyfy things, let's assume that every A,C row is 1 KB.

SELECT * FROM A -- 10^5 rows
JOIN B ON A.Id = B.aId
JOIN C on B.cId = C.Id -- 10^10 rows (every A has 10^5 C)

Let's consider this many-to-many example - EVERY A row is related to 10^5 C rows and we've got 10^5 A rows. So as the result we'll get 100 000 * 100 000 rows. In case of eager-loading, every returned row will contain full information about A and C row so as a result we'll fetch 2 * (100 000 * 100 000) KB = 2*10^10 KB.

If we would use split-query mechanizm in this case we'll load 10^5 rows of A (first query) and 10^10 C (second query) so the result will be 10^5 + 10^10 KB = 1,00005*10^10 KB

As you can see, difference in amount of data fetched from the database is very significant. You have to remember that this example is very exaggerated because ussauly you don't load data without any constrains like this.

I can advise you to always start with eager loading, and then profile if the query have some performance-related issues. In common scenarios, problems related to eager-loading starts when you work with MANY joins and big databases.

Unfortunately, even using ORMs like EF you are not relieved of thinking about the operation of the database.