ORM y el problema N+1

En resumen: Un ORM asigna filas de tablas a objetos y escribe el SQL por ti, y el problema N+1 aparece cuando lanza una consulta extra por cada fila en lugar de una sola.

Problema

La página lenta de pedidos

La página «Pedidos» del panel de administración muestra el número de pedido, la fecha y el nombre del cliente. La tienda tiene 60 pedidos y la página tarda en abrirse. El registro de la base de datos muestra 61 consultas para una sola visita a la página. ¿De dónde salen, si el código no tiene ningún bucle con SQL?

La idea

ORM: una clase en lugar de la tabla

Un ORM (object-relational mapping, «mapeo objeto-relacional») es una biblioteca que enlaza una clase del programa con una tabla de la base de datos. Describes la clase Order y a qué tabla corresponde, y el ORM escribe el SQL por ti cuando pides los pedidos:

Java

@Entity
@Table(name = "orders")
class Order {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @Column(name = "ordered_at")
    private LocalDate orderedAt;
}

List<Order> orders = em.createQuery("SELECT o FROM Order o ORDER BY o.id", Order.class)
    .getResultList();

C#

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public DateOnly OrderedAt { get; set; }
}

var orders = db.Orders.OrderBy(o => o.Id).ToList();

Python

class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped["Customer"] = relationship()
    ordered_at: Mapped[date]


orders = session.scalars(select(Order).order_by(Order.id)).all()

JavaScript

schema.prisma

model Order {
  id         Int      @id
  customerId Int      @map("customer_id")
  customer   Customer @relation(fields: [customerId], references: [id])
  orderedAt  DateTime @map("ordered_at")

  @@map("orders")
}

const orders = await prisma.order.findMany({ orderBy: { id: "asc" } });

Son Hibernate/JPA en Java, Entity Framework Core en C#, SQLAlchemy en Python y Prisma en JavaScript. Ganas menos código repetido, objetos en lugar de filas y el mismo programa para distintas bases de datos. Pagas con SQL que ya no ves: el ORM decide cuántas consultas envía.

Más precisamente, para curiosos

Los ejemplos de ORM no se ejecutan en las comprobaciones del sitio, porque sus paquetes no están allí. Un programa real necesita además algo de configuración para ellos: la dirección de la base de datos, la clase Customer y el contexto (EntityManager, DbContext, Session, PrismaClient).

Míralo

N+1

Aquí está lo mismo sin ORM, pero con el mismo error: primero los pedidos y luego el cliente de cada uno. El programa cuenta las consultas que envía:

Java

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    int queries = 0;
    List<int[]> orders = new ArrayList<>();
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, customer_id FROM orders ORDER BY id")) {
        queries++;
        while (rs.next()) {
            orders.add(new int[] { rs.getInt("id"), rs.getInt("customer_id") });
        }
    }
    for (int[] order : orders) {
        try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM customers WHERE id = ?")) {
            ps.setInt(1, order[1]);
            queries++;
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                System.out.println("pedido " + order[0] + ": " + rs.getString("name"));
            }
        }
    }
    System.out.println("consultas: " + queries);
}

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 7

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var queries = 0;
var orders = new List<(long Id, long CustomerId)>();
var all = conn.CreateCommand();
all.CommandText = "SELECT id, customer_id FROM orders ORDER BY id";
queries++;
using (var reader = all.ExecuteReader())
{
    while (reader.Read())
    {
        orders.Add((reader.GetInt64(0), reader.GetInt64(1)));
    }
}
foreach (var order in orders)
{
    var one = conn.CreateCommand();
    one.CommandText = "SELECT name FROM customers WHERE id = $id";
    one.Parameters.AddWithValue("$id", order.CustomerId);
    queries++;
    Console.WriteLine($"pedido {order.Id}: {one.ExecuteScalar()}");
}
Console.WriteLine($"consultas: {queries}");

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 7

Python

conn = sqlite3.connect(":memory:")
setup(conn)
queries = 1
orders = conn.execute("SELECT id, customer_id FROM orders ORDER BY id").fetchall()
for order_id, customer_id in orders:
    queries += 1
    (name,) = conn.execute("SELECT name FROM customers WHERE id = ?", (customer_id,)).fetchone()
    print(f"pedido {order_id}: {name}")
print("consultas:", queries)
conn.close()

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 7

JavaScript

const db = new DatabaseSync(":memory:");
setup(db);
let queries = 1;
const orders = db.prepare("SELECT id, customer_id FROM orders ORDER BY id").all();
const customer = db.prepare("SELECT name FROM customers WHERE id = ?");
for (const order of orders) {
  queries++;
  console.log(`pedido ${order.id}: ${customer.get(order.customer_id).name}`);
}
console.log("consultas:", queries);
db.close();

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 7

Seis pedidos, siete consultas: una para los pedidos (1) y una para el cliente de cada uno (N). Con 60 pedidos son 61. Con un ORM ocurre lo mismo, solo que el bucle es invisible: o.getCustomer().getName() parece leer un campo, pero en realidad es una consulta.

Java

for (Order o : orders) {
    System.out.println(o.getId() + ": " + o.getCustomer().getName());  // una consulta por cada cliente
}

// La solución: los clientes vienen en la misma consulta.
em.createQuery("SELECT o FROM Order o JOIN FETCH o.customer ORDER BY o.id", Order.class)

C#

// En EF Core la carga diferida está desactivada por defecto: sin Include, Customer es null aquí.
// Con UseLazyLoadingProxies() cada o.Customer se convierte en una consulta aparte.
foreach (var o in orders)
{
    Console.WriteLine($"{o.Id}: {o.Customer.Name}");
}

// La solución:
var withCustomers = db.Orders.Include(o => o.Customer).OrderBy(o => o.Id).ToList();

Python

for o in orders:
    print(f"{o.id}: {o.customer.name}")  # una consulta por cada cliente (lazy="select")

# La solución: los clientes vienen en la misma consulta (JOIN) o en una segunda (selectinload).
orders = session.scalars(select(Order).options(joinedload(Order.customer)).order_by(Order.id)).all()

JavaScript

// Prisma no carga las relaciones por sí solo: obtienes N+1 si las pides en un bucle.
for (const o of orders) {
  const c = await prisma.customer.findUnique({ where: { id: o.customerId } });
  console.log(`${o.id}: ${c.name}`);
}

// La solución:
const withCustomers = await prisma.order.findMany({ include: { customer: true }, orderBy: { id: "asc" } });

La idea

Una sola consulta

La base de datos puede unir ella misma los pedidos y los clientes y devolver todo de una vez:

Java

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    String sql = """
        SELECT o.id, c.name
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        ORDER BY o.id
        """;
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        while (rs.next()) {
            System.out.println("pedido " + rs.getInt("id") + ": " + rs.getString("name"));
        }
    }
    System.out.println("consultas: 1");
}

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 1

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var cmd = conn.CreateCommand();
cmd.CommandText = """
    SELECT o.id, c.name
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    ORDER BY o.id
    """;
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"pedido {reader.GetInt64(0)}: {reader.GetString(1)}");
}
Console.WriteLine("consultas: 1");

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 1

Python

conn = sqlite3.connect(":memory:")
setup(conn)
sql = """
    SELECT o.id, c.name
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    ORDER BY o.id
"""
for order_id, name in conn.execute(sql):
    print(f"pedido {order_id}: {name}")
print("consultas: 1")
conn.close()

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 1

JavaScript

const db = new DatabaseSync(":memory:");
setup(db);
const sql = `
  SELECT o.id, c.name
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  ORDER BY o.id`;
for (const row of db.prepare(sql).all()) {
  console.log(`pedido ${row.id}: ${row.name}`);
}
console.log("consultas: 1");
db.close();

Salida

pedido 1: Radost Hristova
pedido 2: Georgi Petrov
pedido 3: Maria Ivanova
pedido 4: Ivana Koleva
pedido 5: Radost Hristova
pedido 6: Maria Ivanova
consultas: 1

Las mismas filas con una consulta. Para la página de la tienda, esto es todo lo que hace falta:

SQLbase de datos: tienda

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id
LIMIT 10;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id LIMIT 10;

▶ EjecutarRestaurar la consultaBase nueva

La diferencia no está en el SQL de cada consulta, sino en el viaje hasta la base de datos. Cada consulta es un viaje de ida y vuelta por la red, normalmente de menos de un milisegundo dentro de un centro de datos y decenas de milisegundos si la base de datos está lejos. 61 de esos se notan, 6001 paralizan la página.

Pruébalo

Los últimos pedidos

El panel muestra los 5 últimos pedidos: el número, la fecha, el nombre y el correo del cliente, el más reciente primero (con la misma fecha, el número más alto arriba). Con una sola consulta.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Ejercicio

Cuántos pedidos tiene cada cliente

La página «Clientes» muestra a cada cliente con el número de sus pedidos (columna orders), en orden alfabético. Sin N+1, es decir, sin una consulta por cliente, y sin dejar fuera a los clientes que no tienen pedidos.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. Una página muestra 50 pedidos con el nombre del cliente. El programa lee los pedidos con una consulta y luego el cliente de cada uno con otra aparte. ¿Cuántas consultas son?

51502

2. ¿Por qué N+1 no se nota mientras escribes el programa?

Porque en tu computadora la base de datos está vacía o tiene pocas filas, y está en la misma computadoraPorque N+1 solo ocurre con PostgreSQLPorque el ORM lo oculta y no hay forma de verlo

3. ¿Qué arregla N+1 en un ORM?

Pedir la relación en la misma consulta: JOIN FETCH, Include, joinedload, includeMás memoria para la base de datosUn índice en orders.id

4. Un ORM escribe el SQL por ti. ¿Aun así necesitas saber SQL?

Sí: para ver qué escribe, cuando va lento y para los informes que un ORM no sabe describirNo

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: JOIN: dos tablas en una consulta · LEFT JOIN: también las filas sin pareja · Conectarse a la base de datos desde el código · Inyección SQL y parámetros
• Ejercicios sobre el tema: La base de datos en el programa