How to Eliminate the N+1 Query Problem with JOINs in SQLite

N+1 Query Problem Overview

The N+1 query problem occurs when an application first fetches a list of parent records (1 query) and then issues a separate query for each child record (N queries), resulting in N+1 total database round‑trips and significant latency.

Example Schema and Requirement

Two tables are used: categories (id, name) and items (id, name, category_id). The goal is to list all items together with their category names.

Naïve Implementation (N+1 Issue)

import sqlite3

# Connect to the database
conn = sqlite3.connect("example.db")

# Fetch categories
categories_cursor = conn.execute("SELECT * FROM categories;")

for category in categories_cursor.fetchall():
    category_id = category[0]
    category_name = category[1]
    print(f"Category: {category_name}")

    # Fetch items for this category
    items_cursor = conn.execute(
        "SELECT id, name FROM items WHERE category_id = ? ORDER BY name;",
        (category_id,)
    )
    for item in items_cursor.fetchall():
        print(f"  Item ID: {item[0]}, Item Name: {item[1]}")

conn.close()

This approach issues one query for the categories and an additional query for each category's items. With N categories, the total number of queries becomes N+1, leading to slower response times as N grows.

Performance Comparison

With 800 items and 17 categories, the naïve method runs 18 queries and takes about 1 second, whereas a single optimized query can reduce execution time to roughly 0.16 seconds.

Optimizing with JOIN

By using a single SQL JOIN statement, the N+1 problem is eliminated:

import sqlite3

conn = sqlite3.connect("example.db")

query = """
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        i.id AS item_id,
        i.name AS item_name
    FROM categories c
    LEFT JOIN items i ON c.id = i.category_id
    ORDER BY c.name, i.name;
"""
cursor = conn.execute(query)

last_category_id = None
for row in cursor.fetchall():
    category_id, category_name, item_id, item_name = row
    if category_id != last_category_id:
        print(f"Category: {category_name}")
    if item_id is not None:
        print(f"  Item ID: {item_id}, Item Name: {item_name}")
    last_category_id = category_id

conn.close()

This single query returns all categories and their items in one round‑trip, reducing the runtime from 1 second to 0.16 seconds and scaling much better with larger data sets.

Further Data‑Structure Optimization

For scenarios requiring aggregated information such as item counts per category, a GROUP BY query can be used:

query = """
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        COUNT(i.id) AS item_count
    FROM categories c
    LEFT JOIN items i ON c.id = i.category_id
    GROUP BY c.id, c.name
    ORDER BY c.name;
"""

If both counts and detailed item data are needed, the result set can be transformed into a nested dictionary on the application side:

import sqlite3
conn = sqlite3.connect("example.db")
cursor = conn.execute(query)

categories = {}
category_items = []
last_category_id = None
last_category_name = None

for row in cursor.fetchall():
    category_id, category_name, item_id, item_name = row
    if last_category_id is not None and category_id != last_category_id:
        categories[last_category_name] = category_items
        category_items = []
    if item_id is not None:
        category_items.append({"item_id": item_id, "item_name": item_name})
    last_category_id = category_id
    last_category_name = category_name

categories[last_category_name] = category_items

for cat, items in categories.items():
    print(f"Category: {cat}")
    print(f"{len(items)} items")
    for item in items:
        print(f"  Item ID: {item['item_id']}, Item Name: {item['item_name']}")

conn.close()

This approach not only resolves the N+1 issue but also provides a convenient data structure for further processing, such as displaying item counts alongside detailed listings.

Conclusion

The N+1 query pattern is a common performance pitfall in data‑intensive applications. By consolidating queries with JOIN, leveraging aggregation with GROUP BY, and organizing results into nested structures, developers can dramatically improve response times and lay a solid foundation for future feature development.