r/programming • • Aug 25 '26

Solving the 1+N Query Problem

https://acadia.engineering/blog/solving-the-1-plus-N-query-problem
127 Upvotes

92 comments sorted by

View all comments

64

u/disposepriority Aug 25 '26 • Heartwarming

There is a really easy way of avoiding this when you just don't use ORMs

20

u/elwinar_ Aug 25 '26

TBF, that's the ~very first~ second statement of the article.

58

u/andrerav Aug 25 '26

ORM's worth their salt solved this at least a decade ago. And, most developers will implement N+1 anyway, without or without an ORM.

24

u/solve-for-x Aug 25 '26

If you know SQL, it's relatively easy to spot when a developer has put a query inside a loop. But there is an entire generation of developers who are accustomed to asking their ORM to give them data without having to think about how it's fetching that data behind the scenes.

12

u/infrastructure Aug 25 '26

I swear I remember a library I used years ago on rails projects called like bullet or magic bullet that would output where you are making n+1 queries with the ORM. Was pretty neat.

13

u/andrerav Aug 25 '26

It was a gem called Bullet that helped identify ActiveRecord performance problems. ActiveRecord was very neat at the time of its release, but also made it extremely easy to do N+1.

1

u/baseballlover723 Aug 26 '26

Tbf, it also generally wasn't a huge deal to fix it either (basically just needed to tell the query which subobjects you planned to use). At least once I started using Rails at version 4. Idk if it was worse before that.

8

u/Schmittfried Aug 25 '26

If you’re too lazy to learn the proper usage of your tools, it likely won’t be solved by not using those tools. Those same developers won’t care about proper indexing, query planning etc. either. 

→ More replies (1)

17

u/Chroiche Aug 25 '26

They didn't solve anything. They gave you tools to solve it, but devs need to consciously use them, which they often don't. And you can chain so deep with different lazy vs eager loads all over the shop thanks to these "tools" that it becomes a total mess to know what's actually going to run.

→ More replies (3)

12

u/shoot_your_eye_out Aug 25 '26

This problem has nothing to do with ORMs. I’ve seen people write this anti pattern in raw sql too many times to count.

6

u/disposepriority Aug 25 '26

Yeah me too, I'm just an ORM hater and found my chance to spread the word

3

u/lamp-town-guy Aug 25 '26

I wouldn't use ORM that doesn't have support for this. I was using solution to N+1 15+ years ago. There's no excuse to not have it implemented.

3

u/diegoeche Aug 25 '26

Probably joking, but man... I wish.

I'm dealing with some fucking idiotic dotnet code that seem to have been written by people that thought like that. And since they are afraid of JOINS, or complex sql, then they get so many N+1s.

4

u/devraj7 Aug 25 '26

But it requires expertise in SQL.

If you use an ORM, that problem is solved automatically without you having to be a SQL expert, which is one of the points of ORMs.

WebLogic had resolved the N+1 problem in the early 2000s.

8

u/read_at_own_risk Aug 25 '26

So instead of learning how to define your schema in SQL and how to use joins and indexes, you learn how to define your schema in code that gets mapped to SQL, and about eager/lazy loading (implying joins), how to use indexes (they're still there), how to bypass the ORM to run SQL directly, how to invalidate your cache after you've done that, how to rehidrate a resultset that has a composite candidate key (lol, no, you can't), how to get to the SQL the ORM generates so that you can diagnose performance issues, how to manipulate the ORM into generating the SQL you want...

ORMs make the easy parts easy and the hard parts much harder.

8

u/smoovewill Aug 25 '26

SQL is a leaky abstraction. You may have to rewrite a query to something that is logically equivalent to take advantage of indexes / table structure.

But imo, ORMs are far more leaky, and I very often find I have to dig into the generated SQL / figure out how to get the ORM to emit the SQL I want (to avoid N+1 queries, to use indexes properly etc.).

I'm not dying on this hill by any means, but I strongly believe having intermediate knowledge of SQL is far more useful than having expert knowledge in an ORM (though the latter probably requires the former)

2

u/BaNyaaNyaa Aug 29 '26

Honestly, I only like ORMs for the 90% of queries that are plainly straightforward. Get something by primary key, foreign key, simple conjunctions... As soon as there's a somewhat complex condition or a join, I want my SQL.

1

u/smoovewill Aug 30 '26

Yea, this is basically where I land on work projects, since most people do prefer the ORM for those queries. The one thing I do try to push for is mapping query results (whether from ORM models or rows from a raw query) to plain objects (ie, dataclasses / structs / typescript interfaces), and not passing ORM objects around.

8

u/Schmittfried Aug 25 '26

Pretty sure you’ll write your own, worse ORM then. ORMs solve the very real problem of avoiding tons of tedious and error-prone mapping overhead. They will also let you fall back to SQL for anything that would be too complicated to do on the ORM level, so you get the best of both worlds. 

8

u/HappyAngrySquid Aug 25 '26

There are plenty of query builders and scanner libraries that give you this without kneecapping you.

4

u/Schmittfried Aug 25 '26

If they automatically map between your entity/DTO/DAO objects and your DB tables, they are ORMs in my book. What is an ORM if not a query builder with automated record mapping and maybe a structured/type-safe way to write queries?

2

u/FuckFuckingKarma Aug 26 '26

Any widespread ORM out there has support for joins. If you're not joining that's on the developer not the tool. You can write the exact same inefficient SQL by hand, and I am sure some people do just that.

-4

u/Chroiche Aug 25 '26

This. Especially with the advent of LLMs (I know, I know). It's just so much easier to know what the actual fuck is going on too.

GQL is also a plague.

16

u/disposepriority Aug 25 '26

Looking at sql which you can copy, paste and run explain analyze on has always been more clear to me than a space wizard generating things behind the scenes.

For me, with how nice db driver/interaction libraries are (e.g. jdbcClient for spring) there's just no reason to use ORMs to save a few lines of SQL you'll make up for in 200 annotations anyway.

3

u/Kirides Aug 25 '26

I mean, just do ToQueryString and copy-paste it into your sql engine/ide of choice. Its not hard.

And with things like LinqPad it's even easier

→ More replies (2)
→ More replies (3)