Why does my ORM run hundreds of queries to load one page?

Fix it: load related data up front

Every ORM can load associations eagerly. There are two strategies.

JOIN the relation in the same query. This works best for to-one relations (post → author):

Post.objects.select_related("author")                 # Django

Post.includes(:author).references(:author)            # Rails (forces JOIN)

@Query("select p from Post p join fetch p.author")    // JPA

Run a second query with IN (...). This works best for to-many relations (post → tags):

Post.objects.prefetch_related("tags")                 # Django

select(Post).options(selectinload(Post.tags))         # SQLAlchemy

prisma.post.findMany({ include: { tags: true } })     // Prisma

This produces exactly two queries whether there are 10 posts or 10,000:

SELECT * FROM post ORDER BY published_at DESC LIMIT 100;
SELECT * FROM post_tag JOIN tag ... WHERE post_id IN (1, 2, 3, ..., 100);

Don't overcorrect: the cartesian explosion

JOIN-fetching several to-many collections at once multiplies rows. 100 posts × 10 tags × 50 comments is 50,000 rows to return 100 posts. Hibernate refuses to do this with some collection types (MultipleBagFetchException) for exactly this reason. Use one JOIN for to-one relations and separate IN queries for each collection, or Hibernate's @BatchSize / default_batch_fetch_size.

Aggregates: let the database count

post.tags.count() in a loop is another N+1. Ask for the counts in the main query:

Post.objects.annotate(tag_count=Count("tags"))

SELECT p.id, p.title, count(pt.tag_id) AS tag_count
FROM post p
LEFT JOIN post_tag pt ON pt.post_id = p.id
GROUP BY p.id, p.title;

Make it hard to reintroduce

• Turn on strict loading in development and CI so lazy loads raise an error (Rails strict_loading_by_default, Django nplusone with NPLUSONE_RAISE, Hibernate with lazy loading outside a session disabled).
• Add a test that asserts a query count for hot endpoints (assertNumQueries in Django).
• In GraphQL, batch resolvers with DataLoader. N+1 is the default behavior there.

Checklist

1. Count queries per request. Dozens of near-identical queries means N+1.
2. Use JOIN-fetching for to-one relations and IN-prefetching for to-many.
3. Push counts and sums into the main query.
4. Enforce it with strict loading and query-count tests.

TigerGraph Creates Graph Database History by Securing $31 Million in Investment

TigerGraph is set to create a buzz in the graphdatabase ecosystem through the launch of its Native Paralle Graph (NPG) database which is said to have best of both -parallel and non parallel graph databases.

#news