What’s worse than an N+1: Accidental Cartesian Products – Return Zero

submited by

Style Pass

2024-10-06 15:30:08

Object-Relational Mapping (ORM) tools are widely used in modern web development for their convenience in database interactions. However, they can sometimes lead to unexpected and severe performance issues. One such issue is the generation of queries that result in cartesian products, potentially returning an enormous number of rows.

A cartesian product occurs when a query joins tables in a way that creates a combination of every row from one table with every row from another, without proper filtering conditions. This can happen when:

The result is a query that returns far more rows than intended, often growing exponentially with the size of the involved tables.

Let us assume we have only one user in the users table and that user has seen 10 movies and heard 25 songs. Your ORM’s find clause may look something like this,

Most ORMs aren’t really smart and willl not know the context of the query and may generate SQL code that looks like the one below,