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.