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.

References

• Prisma Docs: Query Optimization and Performance
• [Django ORM Documentation: select_related & prefetch_related]
• [N+1 Queries in Django: Tips and Solutions]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20