N+1 Queries: Como corrigir
N+1 queries are a common performance trap when using Object-Relational Mappers (ORMs) such as Django ORM and Prisma in web applications. This article explains what N+1 queries are, why they matter for performance, and practical solutions for fixing them in Django and Prisma-based codebases.
What is an N+1 Query?
An "N+1 query" pattern occurs when an initial query fetches a list of items, then an additional query is executed for each item to fetch related data. This leads to a total of N+1 queries (N being the number of items). This inefficient pattern often arises when ORMs lazily load related objects iteratively inside loops, causing many unnecessary database round-trips and slowing down applications.
Example in Django:
for w in ProgramWorkout.objects.all():
print(w.id, w.program.id) # This triggers a new query for each w.program (N+1 queries)
Here, one query fetches all ProgramWorkout objects, but each access to a related program issues another query, totaling N+1 queries.
| Bad | Nice |
|---|---|
 |
 |
Django Solutions: select_related and prefetch_related
Django provides two powerful ORM tools to solve N+1 issues:
- select_related: Uses SQL JOIN to fetch related single objects in the same query. Ideal for ForeignKey and OneToOne relations.
ProgramWorkout.objects.select_related('program').all() - prefetch_related: Executes an additional query to fetch all related objects for multiple parent items and joins them in Python memory. Best for reverse ForeignKey and ManyToMany relations.
ProgramWorkout.objects.prefetch_related('programworkoutExercise_set').all()
Use select_related for single-valued relations where each item has one related object, and prefetch_related for multi-valued or reverse relations where multiple objects share related items.
Prisma Solutions for N+1 Queries
Similar N+1 problems occur with Prisma when fetching related data in separate queries. Prisma offers multiple approaches to fix this:
- Use include to eager-load related fields in one or minimal queries.
const workouts = await prisma.programWorkout.findMany({ include: { program: true, programWorkoutExercise: true, }, }); - Enable relationLoadStrategy: "join" to perform database joins internally, reducing queries to just one.
- Prisma Client's internal dataloader batches multiple findUnique() queries occurring in the same event loop tick into a single SQL query, optimizing GraphQL resolver patterns.
These techniques reduce network overhead, lower database load, and improve user experience by avoiding excessive queries.
Debugging N+1 Queries
- Django developers can use Django Debug Toolbar to inspect SQL queries per endpoint and detect N+1 patterns.
- Monitoring tools like New Relic can also identify inefficient database call patterns and reveal high query counts per transaction.
- Prisma users can enable query logging or use profiling tools to detect excessive queries and optimize them accordingly.
Best Practices
- Always analyze your code for N+1 query risks when accessing related data in loops.
- Use Django's select_related or prefetch_related, or Prisma's include and relationLoadStrategy appropriately for your data model relationships.
- Profile your APIs regularly with query inspection tools to maintain optimal performance.
