Preventing N+1 Queries with Eager Loading

The N+1 query problem is one of the most common performance issues in database applications. This tutorial shows you how to identify and fix N+1 problems using SIMPLE_SQL's eager loading API.

What is the N+1 Problem?

When you load a list of items and then query related data for each item individually, you end up with N+1 queries: 1 for the list + N for each related record.

N+1 Code Example

-- BAD: This causes N+1 queries!
across db.select_from ("documents").execute as doc loop
    -- This query runs ONCE PER DOCUMENT
    folder := db.select_from ("folders")
        .where ("id = ?", doc.integer_value ("folder_id"))
        .execute.first

    print (doc.string_value ("name") + " in " + folder.string_value ("name"))
end

The Solution: Eager Loading

Eager loading fetches all related data in a single additional query per relationship.

Eager Loading Code Example

-- GOOD: Only 2 queries total!
result := db.eager_loader
    .from_table ("documents")
    .include ("folder", "folders", "folder_id", "id")
    .execute

across result.main_rows as doc loop
    doc_id := doc.integer_64_value ("id")

    -- No additional query - data is already loaded!
    if attached result.first_related_for ("folder", doc_id) as folder then
        print (doc.string_value ("name") + " in " + folder.string_value ("name"))
    end
end

Understanding the Include Pattern

The include method takes four parameters:

include (relation_name, related_table, foreign_key, primary_key)

Common Relationship Types

Belongs-To (N:1)

Document belongs to a folder (many documents per folder):

-- documents.folder_id → folders.id
.include ("folder", "folders", "folder_id", "id")

Has-One (1:1)

User has one profile:

-- profiles.user_id → users.id
-- Note: FK is on related table, so we query from profiles
result := db.eager_loader
    .from_table ("users")
    .include ("profile", "profiles", "id", "user_id")
    .execute

Many-to-Many

Documents have many tags through a join table:

-- documents ↔ document_tags ↔ tags
result := db.eager_loader
    .from_table ("documents")
    .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
    .execute

-- Access tags for each document
across result.main_rows as doc loop
    tags := result.related_for ("tags", doc.integer_64_value ("id"))
    -- tags is a list of all tags for this document
end

Multiple Relationships

Load several relationships in one eager load:

result := db.eager_loader
    .from_table ("documents")
    .columns ("id, name, folder_id, created_by")
    .include ("folder", "folders", "folder_id", "id")
    .include ("author", "users", "created_by", "id")
    .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
    .where ("documents.deleted_at IS NULL")
    .order_by ("documents.created_at DESC")
    .limit (50)
    .execute

-- 4 queries total: documents + folders + users + tags
-- NOT 50 + 50 + 50 = 151 queries!

Detecting N+1 Problems

Use the query monitor to find N+1 patterns in your code:

-- Enable monitoring
db.enable_query_monitor

-- Run your code
display_documents_with_folders

-- Check for warnings
if attached db.query_monitor as mon then
    if mon.has_warnings then
        print ("N+1 WARNING DETECTED:%N")
        print (mon.report)
    else
        print ("OK: " + mon.query_count.out + " queries%N")
    end
end

db.disable_query_monitor

Sample Warning Output

N+1 Query Warning Report
========================
Pattern: SELECT * FROM folders WHERE id = ?
  Executed: 100 times (threshold: 5)

Total patterns with warnings: 1

Accessing Eager-Loaded Data

Single Related Record (Belongs-To)

-- Use first_related_for for belongs-to/has-one
if attached result.first_related_for ("folder", doc_id) as folder then
    print (folder.string_value ("name"))
end

Multiple Related Records (Has-Many)

-- Use related_for for has-many relationships
across result.related_for ("tags", doc_id) as tag loop
    print (tag.string_value ("name") + " ")
end

Count Related Records

tag_count := result.related_count ("tags", doc_id)
print ("Document has " + tag_count.out + " tags")

Best Practices

Performance Impact