ORM and the N+1 problem

In short: An ORM maps table rows to objects and writes the SQL for you, and the N+1 problem is when it runs one extra query for every row instead of a single query.

Problem

The slow orders page

The “Orders” page in the admin panel shows the order number, the date and the customer’s name. The shop has 60 orders, and the page is slow to open. The database log shows 61 queries for one page view. Where do they come from, when the code has no loop with SQL in it?

The idea

ORM: a class instead of the table

An ORM (object-relational mapping) is a library that links a class in the program to a table in the database. You describe the class Order and which table it is, and the ORM writes the SQL for you when you ask for the orders:

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

These are Hibernate/JPA in Java, Entity Framework Core in C#, SQLAlchemy in Python and Prisma in JavaScript. You gain less repetitive code, objects instead of rows, and the same program for different databases. You pay with SQL you no longer see: the ORM decides how many queries to send.

More precisely, if you are curious

The ORM examples are not run in the site's checks, because their packages are not there. A real program also needs some configuration for them: the database address, the Customer class and the context (EntityManager, DbContext, Session, PrismaClient).

See it

N+1

Here is the same thing without an ORM, but with the same mistake: first the orders, then the customer of each. The program counts the queries it sends:

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

Output

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

Output

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

Output

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

Output

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

Six orders, seven queries: one for the orders (1) and one for the customer of each (N). With 60 orders it is 61. With an ORM the same happens, only the loop is invisible: o.getCustomer().getName() looks like reading a field, but it is really a query.

Java

for (Order o : orders) {
    System.out.println(o.getId() + ": " + o.getCustomer().getName());  // a query for every customer
}

// The fix: the customers come in the same query.
em.createQuery("SELECT o FROM Order o JOIN FETCH o.customer ORDER BY o.id", Order.class)

C#

// In EF Core lazy loading is off by default: without Include, Customer is null here.
// With UseLazyLoadingProxies() every o.Customer becomes a separate query.
foreach (var o in orders)
{
    Console.WriteLine($"{o.Id}: {o.Customer.Name}");
}

// The fix:
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}")  # a query for every customer (lazy="select")

# The fix: the customers come in the same query (JOIN) or in one second query (selectinload).
orders = session.scalars(select(Order).options(joinedload(Order.customer)).order_by(Order.id)).all()

JavaScript

// Prisma does not load relations on its own: you get N+1 if you ask for them in a loop.
for (const o of orders) {
  const c = await prisma.customer.findUnique({ where: { id: o.customerId } });
  console.log(`${o.id}: ${c.name}`);
}

// The fix:
const withCustomers = await prisma.order.findMany({ include: { customer: true }, orderBy: { id: "asc" } });

The idea

One query

The database can join the orders and the customers itself and return everything at once:

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

Output

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

Output

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

Output

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

Output

order 1: Radost Hristova
order 2: Georgi Petrov
order 3: Maria Ivanova
order 4: Ivana Koleva
order 5: Radost Hristova
order 6: Maria Ivanova
queries: 1

The same rows with one query. For the shop’s page, this is all it takes:

SQLdatabase: 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;

▶ RunRestore the queryFresh database

The difference is not in the SQL of each query but in the trip to the database. Every query is a round trip over the network, usually under a millisecond inside one data centre, and tens of milliseconds if the database is far away. 61 of those are noticeable, 6,001 bring the page to a halt.

Try it

The latest orders

The dashboard shows the latest 5 orders: the number, the date, the customer’s name and email, the newest first (with the same date, the higher number on top). With one query.

SQLdatabase: shop

Check▶ Run

Exercise

How many orders each customer has

The “Customers” page shows every customer with the number of their orders (column orders), in alphabetical order. Without N+1, that is, without a query for each customer, and without leaving out the customers who have no orders.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. A page shows 50 orders with the customer’s name. The program reads the orders with one query, and then the customer of each with a separate one. How many queries is that?

50251

2. Why does N+1 not show while you are writing the program?

Because N+1 happens only with PostgreSQLBecause on your computer the database is empty or has few rows, and it is on the same computerBecause the ORM hides it and there is no way to see it

3. What fixes N+1 in an ORM?

More memory for the databaseAn index on orders.idAsking for the relation in the same query: JOIN FETCH, Include, joinedload, include

4. An ORM writes the SQL for you. Do you still need to know SQL?

NoYes: to see what it writes, when it is slow, and for the reports an ORM cannot describe

Check

Start the lesson over

Related lessons

• Builds on: JOIN: two tables in one query · LEFT JOIN: rows without a match too · Connecting to the database from code · SQL injection and parameters
• Tasks on this topic: The database in a program