Course contentsShow
← Software Engineering
Lesson 946 of 1,93329. Object-Relational MappingFree lesson

SELECT N+1 in Collections and Loops

Identifying hidden N+1 queries when iterating over collections and applying bulk loading techniques to fix them.

SELECT N+1 in Collections and Loops

What you'll learn: How to spot N+1 queries hiding in loops and iteration, and fix them with bulk loading.

The Hidden Trap

When you load a collection of objects and then loop through them, accessing related data on each one triggers a separate query per item—that's the classic N+1 problem. One query loads the collection (the "1"), then N additional queries fire as you iterate (the "N").

# PROBLEM: N+1 in a loop
users = repository.find_all_users()  # 1 query

for user in users:
    print(user.profile.bio)  # N queries! One per user

This looks innocent but can execute hundreds of queries if users contains many records. Each user.profile access lazily loads that relationship with a new SELECT.

Why Loops Make It Worse

Loops obscure the problem because the queries don't appear explicitly in your code—they're hidden behind property access. Unlike a deliberate find_by_id() call in a loop (which is obviously repetitive), accessing lazy-loaded associations feels like reading a property, not executing SQL.

The Fix: Eager Load Before Iterating

Use eager loading to fetch all related data upfront with JOINs or separate bulk queries:

# SOLUTION: Eager load profiles
users = repository.find_all_users_with_profiles()  # 1 or 2 queries total

for user in users:
    print(user.profile.bio)  # No additional queries!

Now the loop is truly iterating over in-memory objects, not triggering database round-trips.

Key Takeaway: Always eager load associations before looping over collections—hidden lazy loads turn innocent-looking iteration into query storms.