ORM und das N+1-Problem

Kurz gesagt: Ein ORM bildet Tabellenzeilen auf Objekte ab und schreibt das SQL für dich, und das N+1-Problem entsteht, wenn er für jede Zeile eine zusätzliche Abfrage schickt statt einer einzigen.

Problem

Die langsame Bestellseite

Die Seite „Bestellungen“ im Admin-Bereich zeigt die Bestellnummer, das Datum und den Namen des Kunden. Der Shop hat 60 Bestellungen, und die Seite öffnet sich langsam. Das Log der Datenbank zeigt 61 Abfragen für eine Seitenansicht. Woher kommen sie, wenn im Code keine Schleife mit SQL steht?

Die Idee

ORM: eine Klasse statt der Tabelle

Ein ORM (Object-Relational Mapping) ist eine Bibliothek, die eine Klasse im Programm mit einer Tabelle in der Datenbank verbindet. Du beschreibst die Klasse Order und welche Tabelle sie ist, und der ORM schreibt das SQL für dich, wenn du die Bestellungen anforderst:

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" } });

Das sind Hibernate/JPA in Java, Entity Framework Core in C#, SQLAlchemy in Python und Prisma in JavaScript. Du gewinnst weniger wiederholten Code, Objekte statt Zeilen und dasselbe Programm für verschiedene Datenbanken. Du bezahlst mit SQL, das du nicht mehr siehst: Der ORM entscheidet, wie viele Abfragen er schickt.

Genauer, für Neugierige

Die ORM-Beispiele werden in den Prüfungen der Seite nicht ausgeführt, weil ihre Pakete dort nicht vorhanden sind. Ein echtes Programm braucht für sie außerdem etwas Konfiguration: die Adresse der Datenbank, die Klasse Customer und den Kontext (EntityManager, DbContext, Session, PrismaClient).

Sieh es dir an

N+1

Hier ist dasselbe ohne ORM, aber mit demselben Fehler: erst die Bestellungen, dann der Kunde jeder Bestellung. Das Programm zählt die Abfragen, die es schickt:

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("Bestellung " + order[0] + ": " + rs.getString("name"));
            }
        }
    }
    System.out.println("Abfragen: " + queries);
}

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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($"Bestellung {order.Id}: {one.ExecuteScalar()}");
}
Console.WriteLine($"Abfragen: {queries}");

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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"Bestellung {order_id}: {name}")
print("Abfragen:", queries)
conn.close()

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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(`Bestellung ${order.id}: ${customer.get(order.customer_id).name}`);
}
console.log("Abfragen:", queries);
db.close();

Ausgabe

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

Sechs Bestellungen, sieben Abfragen: eine für die Bestellungen (1) und eine für den Kunden jeder Bestellung (N). Bei 60 Bestellungen sind es 61. Mit einem ORM passiert dasselbe, nur ist die Schleife unsichtbar: o.getCustomer().getName() sieht aus wie das Lesen eines Feldes, ist aber in Wirklichkeit eine Abfrage.

Java

for (Order o : orders) {
    System.out.println(o.getId() + ": " + o.getCustomer().getName());  // eine Abfrage für jeden Kunden
}

// Die Lösung: Die Kunden kommen in derselben Abfrage mit.
em.createQuery("SELECT o FROM Order o JOIN FETCH o.customer ORDER BY o.id", Order.class)

C#

// In EF Core ist Lazy Loading standardmäßig aus: Ohne Include ist Customer hier null.
// Mit UseLazyLoadingProxies() wird jedes o.Customer zu einer eigenen Abfrage.
foreach (var o in orders)
{
    Console.WriteLine($"{o.Id}: {o.Customer.Name}");
}

// Die Lösung:
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}")  # eine Abfrage für jeden Kunden (lazy="select")

# Die Lösung: Die Kunden kommen in derselben Abfrage (JOIN) oder in einer zweiten (selectinload).
orders = session.scalars(select(Order).options(joinedload(Order.customer)).order_by(Order.id)).all()

JavaScript

// Prisma lädt Relationen nicht von selbst: Du bekommst N+1, wenn du sie in einer Schleife abfragst.
for (const o of orders) {
  const c = await prisma.customer.findUnique({ where: { id: o.customerId } });
  console.log(`${o.id}: ${c.name}`);
}

// Die Lösung:
const withCustomers = await prisma.order.findMany({ include: { customer: true }, orderBy: { id: "asc" } });

Die Idee

Eine Abfrage

Die Datenbank kann die Bestellungen und die Kunden selbst verbinden und alles auf einmal zurückgeben:

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("Bestellung " + rs.getInt("id") + ": " + rs.getString("name"));
        }
    }
    System.out.println("Abfragen: 1");
}

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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($"Bestellung {reader.GetInt64(0)}: {reader.GetString(1)}");
}
Console.WriteLine("Abfragen: 1");

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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"Bestellung {order_id}: {name}")
print("Abfragen: 1")
conn.close()

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 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(`Bestellung ${row.id}: ${row.name}`);
}
console.log("Abfragen: 1");
db.close();

Ausgabe

Bestellung 1: Radost Hristova
Bestellung 2: Georgi Petrov
Bestellung 3: Maria Ivanova
Bestellung 4: Ivana Koleva
Bestellung 5: Radost Hristova
Bestellung 6: Maria Ivanova
Abfragen: 1

Dieselben Zeilen mit einer Abfrage. Für die Seite des Shops reicht das:

SQLDatenbank: Shop

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;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Unterschied liegt nicht im SQL jeder Abfrage, sondern im Weg zur Datenbank. Jede Abfrage ist ein Hin und Zurück übers Netzwerk, in einem Rechenzentrum meist unter einer Millisekunde und, wenn die Datenbank weit weg ist, einige zehn Millisekunden. 61 davon merkt man, 6001 bringen die Seite zum Stillstand.

Probier es aus

Die neuesten Bestellungen

Das Dashboard zeigt die neuesten 5 Bestellungen: Nummer, Datum, Name und E-Mail-Adresse des Kunden, die neueste zuerst (bei gleichem Datum die höhere Nummer oben). Mit einer Abfrage.

SQLDatenbank: Shop

Prüfen▶ Ausführen

Übung

Wie viele Bestellungen jeder Kunde hat

Die Seite „Kunden“ zeigt jeden Kunden mit der Zahl seiner Bestellungen (Spalte orders), in alphabetischer Reihenfolge. Ohne N+1, also ohne eine Abfrage pro Kunde, und ohne die Kunden auszulassen, die keine Bestellungen haben.

SQLDatenbank: Shop

Prüfen▶ Ausführen

Test

Teste dich

1. Eine Seite zeigt 50 Bestellungen mit dem Namen des Kunden. Das Programm liest die Bestellungen mit einer Abfrage und danach den Kunden jeder Bestellung mit einer eigenen. Wie viele Abfragen sind das?

25051

2. Warum fällt N+1 beim Schreiben des Programms nicht auf?

Weil N+1 nur mit PostgreSQL passiertWeil auf deinem Computer die Datenbank leer ist oder wenige Zeilen hat und auf demselben Computer liegtWeil der ORM es versteckt und man es nicht sehen kann

3. Was behebt N+1 in einem ORM?

Ein Index auf orders.idDie Beziehung in derselben Abfrage anfordern: JOIN FETCH, Include, joinedload, includeMehr Speicher für die Datenbank

4. Ein ORM schreibt das SQL für dich. Musst du trotzdem SQL können?

NeinJa: um zu sehen, was er schreibt, wenn es langsam ist, und für die Auswertungen, die ein ORM nicht beschreiben kann

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: JOIN: zwei Tabellen in einer Abfrage · LEFT JOIN: auch Zeilen ohne Partner · Aus dem Code mit der Datenbank verbinden · SQL-Injection und Parameter
• Aufgaben zum Thema: Die Datenbank im Programm