@Ajit5ingh

N+1 Query Problem

The database performance killer every developer should know

What is the N+1 Query Problem?

The N+1 query problem happens when your code executes 1 query to get N records, then N additional queries to get related data for each record. Instead of 1 query, you end up with N+1 queries, killing your database performance.

The Problem in Action

Bad: N+1 Queries

const users = await User.findAll();

for (const user of users) {
  const posts = await Post.findByUserId(user.id);
  console.log(user.name, posts.length);
}
            

Total Queries: 1 + 100 = 101 queries

Problem: If you have 100 users, this code will execute 101 database queries!

Good: Single Query

const users = await User.findAll({
  include: [Post]
});

for (const user of users) {
  console.log(user.name, user.posts.length);
}
            

Total Queries: 1 query

Solution: Eager loading gets all the data you need in a single optimized query.

Query Execution Flow


graph TD
  A[Application Request] --> B{Query Strategy}
  
  B -->|N+1 Problem| C[Get Users Query]
  C --> D[User 1 Found]
  D --> E[Query Posts for User 1]
  C --> F[User 2 Found]
  F --> G[Query Posts for User 2]
  C --> H[User N Found]
  H --> I[Query Posts for User N]
  E --> J[101 Total Queries!]
  G --> J
  I --> J
  
  B -->|Optimized| K[Single JOIN Query]
  K --> L[Users + Posts Retrieved]
  L --> M[1 Total Query!]
  
  J --> N[Slow Response]
  M --> O[Fast Response]
  
  style A fill:#f1f5f9,stroke:#64748b,stroke-width:2px
  style B fill:#e0f2fe,stroke:#0284c7,stroke-width:2px
  style C fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style D fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style E fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style F fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style G fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style H fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style I fill:#f8fafc,stroke:#64748b,stroke-width:2px
  style J fill:#fee2e2,stroke:#dc2626,stroke-width:2px
  style K fill:#dcfce7,stroke:#16a34a,stroke-width:2px
  style L fill:#dcfce7,stroke:#16a34a,stroke-width:2px
  style M fill:#dcfce7,stroke:#16a34a,stroke-width:2px
  style N fill:#fee2e2,stroke:#dc2626,stroke-width:2px
  style O fill:#dcfce7,stroke:#16a34a,stroke-width:2px

Performance Impact

808ms

N+1 Queries (100 users)

101 round trips to database

15ms

Optimized Query

1 round trip to database

That's a 54x performance improvement!

📊 How these numbers were calculated:

Assumptions: 8ms per database query (5ms network + 3ms execution)

N+1 Approach: 1 user query (8ms) + 100 post queries (800ms) = 808ms

Optimized: 1 JOIN query (15ms, slightly more complex)

Improvement: 808ms ÷ 15ms = 54x faster

Common Solutions

Eager Loading

Load related data upfront in a single query using JOIN operations.

Sequelize (Node.js):

const users = await User.findAll({
  include: [Post, Profile]
});
          

ActiveRecord (Rails):

users = User.includes(:posts, :profile)

Entity Framework (C#):

var users = context.Users
  .Include(u => u.Posts)
  .Include(u => u.Profile);
          

Batch Loading

Collect all IDs first, then query related data in batches.

const users = await User.findAll();
const userIds = users.map(u => u.id);

const posts = await Post.findAll({
  where: { userId: userIds }
});

const postsByUser = posts.reduce((acc, post) => {
  acc[post.userId] = acc[post.userId] || [];
  acc[post.userId].push(post);
  return acc;
}, {});
          

DataLoader Pattern

Automatically batch and cache database requests (popular in GraphQL).

const postLoader = new DataLoader(async (userIds) => {
  const posts = await Post.findAll({
    where: { userId: userIds }
  });
  
  return userIds.map(id => 
    posts.filter(p => p.userId === id)
  );
});

const userPosts = await postLoader.load(userId);
          