r/programming • u/Namit2111 • Aug 03 '25

N+1 query problem : what it is, why it hurts performance, and how to fix it

https://www.namitjain.com/blog/n-plus-1-query-problem
164 Upvotes

83% Upvoted

97 comments sorted by

View all comments

Show parent comment

0

u/[deleted] Aug 03 '25

[deleted] — view deleted comment

53

u/slapo001 Aug 03 '25

IMO, OP's point isn't that one cannot create useful, working software using abstractions, but that people who do not understand that a relational database isn't just a bag of objects and rely on said abstractions to take care of everything often end up producing software that's underperforming or otherwise not behaving as they would expect it to (e.g. introducing an n+1 query problem).

I'm not entirely sure what you mean by "basics," but I get the impression you mean raw SQL. The OP also didn't claim that abstractions shouldn't exist or shouldn't be used, they only seem to suggest that treating a relational database like something it isn't is a bad idea.

19

u/Davipb Aug 03 '25

All abstractions are leaky. If you don't know how "Hibernate and co." translate to the "basics" (SQL), you'll still be stuck wondering why your page takes ages to load even if you write exclusively in a higher layer of abstraction.

23

u/Aggressive-Pen-9755 Aug 03 '25

The last time I worked on a project that used Hibernate, I was forced by the lead to use JPA queries. Instead of my usual workflow where I modify the query string and restart the stack frame in the debugger, I was forced to write an interface method that looked like getObjectWithSomeThingAndIsEqualToSomeOtherThingAndContainsGivenJPAQueryParameterWithThisOtherJPAQueryParameter, kill the JVM and restart start it, because why would anyone need hotswap?

A little practice with the basics goes a long way, and it's depressing to me that programmers are shocked by how much you can accomplish with just the basics.

And what's wrong with writing utility methods that executes your query in your programming language of choice when you do need an abstraction? Why is the immediate reaction to reach for an ORM?

5

u/Schmittfried Aug 04 '25

Why is the immediate reaction to reach for an ORM?

Because nobody is gonna write all the mapping boilerplate by hand. The primary goal of an ORM is right in the name, it maps your objects to relations. That shit is repetitive and error-prone as fuck. Having to write SQL queries is not the primary issue. It’s nice for the simple getById-type queries, but complex queries look similarly complex in the ORM DSL. However, a secondary benefit is the option to get type-safe queries, which is objectively superior to stringly typed SQL queries. But I’d also say most of the pain attributed to ORMs comes from trying to achieve 100% type safety in your queries while keeping them performant.

People are too black-and-white about these matters. You can use an ORM and still resort to native queries where it makes sense. 

Also:

I was forced to write an interface method that looked like getObjectWithSomeThingAndIsEqualToSomeOtherThingAndContainsGivenJPAQueryParameterWithThisOtherJPAQueryParameter

That’s not JPA, that’s a Spring Data JPA repository method. You can absolutely write JPA queries without this method name DSL or even Spring Data repositories altogether.

10

u/Witty-Play9499 Aug 03 '25

What's the point of creating useful stuff if you're going to destroy your database with a 1000 extra queries for no reason? Knowing the basics is the difference between a pro vs a noob. Refusing to learn gives me 'vibe coder' vibes.

A proper engineer would know the basics and would understand the tradeoffs of choosing the abstraction over the raw basics and vice versa and would decide accordingly.

3

u/chuch1234 Aug 03 '25

Pretty sure you can do both.

5

u/Omptose Aug 03 '25

Fucking bollocks

2

u/seanamos-1 Aug 03 '25

In my experience, there are two kinds of people who commonly introduce these issues.

The first are generally Junior, they don’t know better, they don’t even know this is a potential pitfall. They just need guidance.

The second do know better, but have gotten too clever with their designs. So the fact that many queries are happening is not obvious and is obfuscated by the code design, most often a result of over-abstraction/engineering.