N+1 Query Problem

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Python with SQLAlchemy ORM - THE PROBLEM
 
# Query 1: Fetch all posts
posts = session.query(Post).all()
 
for post in posts:
    # Query 2 through N+1: For each post, fetch the author
    print(f"Title: {post.title}")
    print(f"Author: {post.author.name}")  # <-- Triggers lazy load!
    #                 ^^^^^^^^^^^
    # SQLAlchemy executes: SELECT * FROM authors WHERE id = ?
    # This happens for EVERY post in the loop!
 
# If we have 100 posts:
# Query 1: SELECT * FROM posts;                    (1 query)
# Query 2: SELECT * FROM authors WHERE id = 1;    
# Query 3: SELECT * FROM authors WHERE id = 2;    
# Query 4: SELECT * FROM authors WHERE id = 3;    
# ...
# Query 101: SELECT * FROM authors WHERE id = 100; (100 queries)
# ─────────────────────────────────────────────────
# TOTAL: 101 queries for what should be 1-2 queries!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# Python example with raw SQL
 
# PROBLEM: Query in a loop
cursor.execute("SELECT id, title, author_id FROM posts")
posts = cursor.fetchall()
 
for post in posts:
    # Executing a query for each post - N+1!
    cursor.execute(
        "SELECT name FROM authors WHERE id = %s", 
        (post['author_id'],)
    )
    author = cursor.fetchone()
    print(f"{post['title']} by {author['name']}")
 
# SOLUTION 1: Single JOIN query
cursor.execute("""
    SELECT p.id, p.title, a.name as author_name
    FROM posts p
    JOIN authors a ON p.author_id = a.id
""")
posts_with_authors = cursor.fetchall()
for row in posts_with_authors:
    print(f"{row['title']} by {row['author_name']}")
# 1 query instead of N+1
 
# SOLUTION 2: Batch with IN clause
cursor.execute("SELECT id, title, author_id FROM posts")
posts = cursor.fetchall()
author_ids = tuple(set(p['author_id'] for p in posts))
 
cursor.execute(
    "SELECT id, name FROM authors WHERE id IN %s",
    (author_ids,)
)
authors = {a['id']: a['name'] for a in cursor.fetchall()}
 
for post in posts:
    print(f"{post['title']} by {authors[post['author_id']]}")
# 2 queries regardless of N

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

-- PROBLEM: Stored procedure with cursor loop
CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE customer_name VARCHAR(100);
    
    -- Cursor to iterate through orders
    DECLARE order_cursor CURSOR FOR 
        SELECT id FROM orders WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN order_cursor;
    
    read_loop: LOOP
        FETCH order_cursor INTO order_id;
        IF done THEN LEAVE read_loop; END IF;
        
        -- N+1: Query per order to get customer name
        SELECT name INTO customer_name 
        FROM customers 
        WHERE id = (SELECT customer_id FROM orders WHERE id = order_id);
        
        -- Process the order...
    END LOOP;
    
    CLOSE order_cursor;
END;
 
-- SOLUTION: Avoid cursor, use set-based operations
CREATE PROCEDURE ProcessOrdersBetter()
BEGIN
    -- Get all data in one query
    DECLARE result CURSOR FOR
        SELECT o.id, o.total, c.name as customer_name
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        WHERE o.status = 'pending';
    
    -- Or better: Avoid cursors entirely with set-based logic
    -- Most cursor operations can be rewritten as UPDATE/INSERT with JOIN
    
    UPDATE orders o
    JOIN customers c ON o.customer_id = c.id
    SET o.processed_by = c.sales_rep,
        o.status = 'processing'
    WHERE o.status = 'pending';
    -- Single set-based operation, no N+1
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

# ==========================================
# Python: Query Counting in Tests
# ==========================================
 
from sqlalchemy import event
from sqlalchemy.engine import Engine
import threading
 
# Thread-local query counter
query_counter = threading.local()
 
@event.listens_for(Engine, "before_cursor_execute")
def count_query(conn, cursor, statement, parameters, context, executemany):
    if not hasattr(query_counter, 'count'):
        query_counter.count = 0
    query_counter.count += 1
 
# Context manager for counting queries
from contextlib import contextmanager
 
@contextmanager
def count_queries():
    query_counter.count = 0
    yield query_counter
    
# Usage in tests
def test_no_n1_in_post_list():
    with count_queries() as counter:
        posts = session.query(Post).options(joinedload(Post.author)).all()
        for post in posts:
            _ = post.author.name
    
    # Assert reasonable query count (should be 1 or 2, not N+1)
    assert counter.count <= 2, f"N+1 detected: {counter.count} queries"
 
# ==========================================
# Django: django-debug-toolbar
# ==========================================
# In settings.py:
INSTALLED_APPS = [
    ...
    'debug_toolbar',
]
 
# The toolbar shows query count and detects duplicates
 
# ==========================================
# Rails: bullet gem
# ==========================================
# In Gemfile:
# gem 'bullet', group: 'development'
 
# In config/environments/development.rb:
# Bullet.enable = true
# Bullet.alert = true  # Browser alert on N+1
# Bullet.add_footer = true  # Show in page footer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- PostgreSQL: Find potential N+1 from query patterns
-- Look for repeated similar queries in pg_stat_statements
 
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    calls * mean_exec_time AS total_impact
FROM pg_stat_statements
WHERE query LIKE 'SELECT%WHERE%id = $1%'  -- Parameterized single-row lookups
ORDER BY calls DESC
LIMIT 20;
 
-- High "calls" with low "mean_exec_time" suggests N+1
-- e.g., 10,000 calls of 0.5ms each = N+1 pattern
 
-- MySQL: Enable general query log temporarily
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';
 
-- Review queries
SELECT * FROM mysql.general_log 
ORDER BY event_time DESC LIMIT 1000;
 
-- Look for patterns like:
-- SELECT * FROM authors WHERE id = 1;
-- SELECT * FROM authors WHERE id = 2;  
-- SELECT * FROM authors WHERE id = 3;
-- ...
 
-- SQL Server: Query Store
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs.count_executions,
    rs.avg_duration / 1000 AS avg_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%WHERE%id%=%'
ORDER BY rs.count_executions DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- ==========================================
-- Strategy 1: JOIN-based loading
-- ==========================================
 
-- Load posts with authors in single query
SELECT 
    p.id AS post_id,
    p.title,
    p.content,
    a.id AS author_id,
    a.name AS author_name,
    a.email AS author_email
FROM posts p
JOIN authors a ON p.author_id = a.id;
 
-- Result: One row per post, with author embedded
-- ORM hydrates this into Post objects with populated Author
 
-- ==========================================
-- Strategy 2: Batch/IN Clause loading
-- ==========================================
 
-- Query 1: Get all posts
SELECT id, title, content, author_id FROM posts;
 
-- Application collects author_ids: [1, 2, 3, 5, 8, 13, ...]
 
-- Query 2: Get all authors for those posts
SELECT id, name, email 
FROM authors 
WHERE id IN (1, 2, 3, 5, 8, 13, ...);
 
-- Application merges in memory
-- 2 queries total, regardless of post count
 
-- ==========================================
-- Avoiding Cartesian Explosion
-- ==========================================
 
-- PROBLEM: Multiple JOINs on collections
SELECT 
    p.id, p.title,
    c.id AS comment_id, c.text,
    t.id AS tag_id, t.name
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id;
 
-- If post has 10 comments and 5 tags:
-- Result: 10 × 5 = 50 rows per post!
-- 100 posts × 50 rows = 5,000 total rows
-- This is called "cartesian explosion"
 
-- SOLUTION: Separate queries for each collection
SELECT id, title FROM posts;                                   -- 1 query
SELECT id, text, post_id FROM comments WHERE post_id IN (...); -- 1 query
SELECT t.id, t.name, pt.post_id 
FROM tags t 
JOIN post_tags pt ON t.id = pt.tag_id 
WHERE pt.post_id IN (...);                                     -- 1 query
-- 3 compact result sets, no cartesian explosion

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

// JavaScript/TypeScript DataLoader Example
 
import DataLoader from 'dataloader';
 
// Create a DataLoader for authors
const authorLoader = new DataLoader(async (authorIds: number[]) => {
    // This function is called once with ALL requested IDs batched together
    console.log(`Loading authors: ${authorIds}`);
    
    // Single database query for all requested authors
    const authors = await db.query(
        'SELECT * FROM authors WHERE id IN (?)',
        [authorIds]
    );
    
    // Return authors in same order as requested IDs
    const authorsById = new Map(authors.map(a => [a.id, a]));
    return authorIds.map(id => authorsById.get(id) || null);
});
 
// Usage in resolvers
async function getPostsWithAuthors() {
    const posts = await db.query('SELECT * FROM posts');
    
    // Even though we call load() N times, DataLoader batches into 1 query
    const postsWithAuthors = await Promise.all(
        posts.map(async (post) => ({
            ...post,
            author: await authorLoader.load(post.authorId)  // Batched!
        }))
    );
    
    return postsWithAuthors;
}
 
// What happens:
// 1. Posts query executes (1 query)
// 2. 100 calls to authorLoader.load() accumulate
// 3. DataLoader batches into: SELECT * FROM authors WHERE id IN (1,2,3,4,...)
// 4. Total: 2 queries instead of 101
 
// ==========================================
// DataLoader with Caching
// ==========================================
 
// DataLoader also caches within a request
const posts = [
    { id: 1, authorId: 5 },
    { id: 2, authorId: 5 },  // Same author as post 1
    { id: 3, authorId: 5 },  // Same author again
];
 
// Only loads author 5 once, returns cached result for subsequent calls
posts.forEach(async (post) => {
    const author = await authorLoader.load(post.authorId);
    // First call: loads from DB
    // Subsequent calls: returns cached
});

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

# Python: Manual Batching Pattern
 
from collections import defaultdict
 
def get_posts_with_details(post_ids):
    """
    Fetch posts with authors and comments using manual batching.
    Avoids N+1 by batching related entity lookups.
    """
    
    # Step 1: Load all posts
    posts = db.query(
        "SELECT * FROM posts WHERE id IN %s", 
        (tuple(post_ids),)
    )
    
    if not posts:
        return []
    
    # Step 2: Extract foreign keys for batch loading
    author_ids = list(set(p['author_id'] for p in posts))
    
    # Step 3: Batch load all authors
    authors = db.query(
        "SELECT * FROM authors WHERE id IN %s",
        (tuple(author_ids),)
    )
    authors_by_id = {a['id']: a for a in authors}
    
    # Step 4: Batch load all comments
    comments = db.query(
        "SELECT * FROM comments WHERE post_id IN %s",
        (tuple(p['id'] for p in posts),)
    )
    comments_by_post = defaultdict(list)
    for comment in comments:
        comments_by_post[comment['post_id']].append(comment)
    
    # Step 5: Assemble results
    result = []
    for post in posts:
        result.append({
            **post,
            'author': authors_by_id.get(post['author_id']),
            'comments': comments_by_post.get(post['id'], [])
        })
    
    return result
 
# Total queries: 3 (posts, authors, comments)
# Instead of: 1 + N (posts) + N (comments) = 2N + 1 queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

# ==========================================
# Python/Django: Prevent by detecting at runtime
# ==========================================
 
# settings.py - strict mode for catching N+1 in development
if DEBUG:
    # Raise exception on lazy loading (forces explicit eager loading)
    import warnings
    warnings.filterwarnings(
        'error',
        r".*accessing.*lazy.*",
        category=DeprecationWarning
    )
 
# ==========================================
# SQLAlchemy: Disable lazy loading globally
# ==========================================
 
from sqlalchemy.orm import configure_mappers
 
# After all models are defined
configure_mappers()
 
# Make all relationships "raise" on lazy access
for mapper in Base.registry.mappers:
    for rel in mapper.relationships:
        rel.lazy = 'raise'  # Raises exception if accessed without loading
 
# Now accessing unloaded relationships raises an error:
posts = session.query(Post).all()
posts[0].author.name  # InvalidRequestError: 'Post.author' is not available
 
# Force explicit loading:
posts = session.query(Post).options(joinedload(Post.author)).all()
posts[0].author.name  # Works
 
# ==========================================
# Testing: Query Count Assertions
# ==========================================
 
import pytest
from django.test.utils import CaptureQueriesContext
 
@pytest.mark.django_db
def test_post_list_query_count(client, django_assert_max_num_queries):
    # Create test data
    create_posts(count=50)
    
    # Assert maximum query count
    with django_assert_max_num_queries(3):  # posts + authors + comments
        response = client.get('/api/posts/')
    
    assert response.status_code == 200
    # If more than 3 queries execute, test fails with actual count
 
# ==========================================
# Logging: Make queries visible
# ==========================================
 
# Django settings.py
LOGGING = {
    'version': 1,
    'handlers': {
        'console': {'class': 'logging.StreamHandler'},
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',  # Shows all SQL
            'handlers': ['console'],
        },
    },
}