N+1 Query Problem Detection and Fix

In this tutorial, you'll learn about N+1 Query Problem Detection and Fix. We cover key concepts, practical examples, and best practices.

Your API endpoint loads 10 users but makes 11 database queries — 1 query for the list of users and 10 more queries for each user's orders. This is the N+1 query problem, where lazy loading causes a query for every parent-child relationship.

Step-by-Step Fix

1. Detect N+1 queries in Django

from django.db import connection

# Wrong — lazy loading causes N+1 queries
def get_users_with_orders():
    users = User.objects.all()  # 1 query
    for user in users:
        orders = user.order_set.all()  # N queries!
        print(f"{user.name}: {len(orders)} orders")
    # Total: 1 + N queries

# Check the number of queries
print(f"Queries: {len(connection.queries)}")
# Queries: 11 (for 10 users)

2. Fix with select_related (for FK/O2O)

# Right — use select_related for foreign key relationships
def get_users_with_orders():
    users = User.objects.select_related("profile").all()
    # 1 query with JOIN
    for user in users:
        print(f"{user.name}: {user.profile.bio}")
    # Total: 1 query

3. Fix with prefetch_related (for M2M/reverse FK)

# Right — use prefetch_related for many-to-many and reverse relations
def get_users_with_orders():
    users = User.objects.prefetch_related("order_set").all()  # 1 + 1 queries
    for user in users:
        orders = user.order_set.all()  # Uses prefetched cache
        print(f"{user.name}: {len(orders)} orders")
    # Total: 2 queries (1 for users, 1 for all orders)

4. Fix in Rails ActiveRecord

# Wrong — N+1 queries
users = User.all  # 1 query
users.each do |user|
  puts "#{user.name}: #{user.orders.count}"  # N queries
end

# Right — eager loading
users = User.includes(:orders)  # 2 queries with join
users.each do |user|
  puts "#{user.name}: #{user.orders.length}"  # Uses cache
end

5. Fix in Prisma (Node.js)

// Wrong — N+1 queries
const users = await prisma.user.findMany();
// 1 query
for (const user of users) {
  const orders = await prisma.order.findMany({
    where: { userId: user.id }
  });
  // N queries!
}

// Right — include relations in one query
const users = await prisma.user.findMany({
  include: {
    orders: true,  // JOIN in a single query
  },
});
// 1 query total

Common Mistakes

Prevention

• Use tools like django-debug-toolbar, Rails Panel, or Prisma Studio to detect N+1 queries.
• Set database query count alerts in development: warn when a single request makes >10 queries.
• Use ORM-specific tools: nplusone (Python), bullet (Rails), prisma-metrics (Node.js).
• Always eager-load relationships that will be accessed in views/templates.
• Review query counts in code review.

DodaTech Tools

Doda Browser's query analyzer counts database calls per page load and flags N+1 patterns automatically. DodaZIP archives query logs for performance regression reviews. Durga Antivirus Pro uses efficient batch queries to avoid N+1 in its threat intelligence lookups.

Common Mistakes with query problem

1. Using return to exit a function early instead of wrapping a pure value in the monad
2. Mixing let bindings with <- bindings in do notation, producing type errors
3. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors

These mistakes appear frequently in real-world N+1 code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ