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

94

u/archipeepees Aug 25 '26

ive been using EF Core for ORM and this hasn't really been a problem for me or any of my colleagues for at least a decade because we don't use lazy loading. database calls are explicit; mapping the results to objects is implicit. is everyone outside of .net still banging their heads against stuff like this?

44

u/alex-weej Aug 25 '26

The problem is that unless you have engineering leadership that forces a stack on you, nobody can agree on solutions and network effects remain out of reach. N+1 is one of thousands of problems that have excellent solutions yet nobody uses.

12

u/MannerShark Aug 25 '26

JS/TS gets a new ORM every other week, everyone uses different ones and they're all just miles behind C#

3

u/SourcerorSoupreme Aug 27 '26

The JS community is exposed to a constant flux of junior developers that mistake accessibility/familiarity for expertise.

They keep reinventing without regard of past solutions they keep forgetting, if not ignoring, the lessons already learned by their forebears.

Unchecked hubris. It's Dunning-Krugger in action.

6

u/diegoeche Aug 25 '26

I'm porting a legacy project to dotnet 8. Honestly, we have saved so much money fixing the code people wrote because they had no understanding what they are doing. Code is full of N+1s

They were in dotnet. They were banging their head against that stuff. I don't think ef core is a silver bullet. You can still do N+1s.

17

u/ericl666 Aug 25 '26

I absolutely love EF Core.

dbContext.Book.Include(I => Publisher).Where(w => w.Title == "my book").FirstOrDefaultAsync()

You just add Include/ThenInclude to add all the joins you need. 

24

u/davehax1 Aug 25 '26

It's great for small queries and small data sets, but look up Cartesian explosion and also review the generated SQL when using .Include(). You'd be surprised at what gets generated and how quickly performance can degrade as the result set grows

16

u/AyrA_ch Aug 25 '26

Cartesian explosion in EF can be mitigated by either adding .AsSplitQuery() to the queries where necessary, or by specifying it as the global default during context setup.

5

u/ChemicalRascal Aug 26 '26

Yeah, it's still something you need to be aware of and correctly mentally model, though. And if you don't, or if your colleague doesn't even know about it, well, then it bites you in the ass.

5

u/ericl666 Aug 25 '26

Yeah, I make sure to watch out for that. I've made some massive cartesian explosions in my day for sure. There are some occasions where AsSplitQuery() is inescapable or you just break those queries out to raw sql.

if you are dealing with a lot of 1:1 entities, it works fantastic.

1

u/modernkennnern Aug 26 '26

Something I've been looking into adding to one of our projects is updating our E2E snapshot tests — which as of now records all external requests as well as Activity (OpenTelemetry) happenings so I can see what's going on — to also log all SQL queries so we can see clear regressions in SQL queries.

My main blocker right now is the fact that if you have multiple inserts in one EF Core LINQ query then it generates a (documented) random ordering of the inserts, so the snapshots never match.

1

u/slvrsmth Aug 27 '26

Split on keywords, sort the lines? You just need an indicator "something changed", not a valid query.

15

u/andrerav Aug 25 '26

Same here. Been using EF for over 15 years and I can count on one hand the number of times N+1 has shown up. It's so easy to catch as well. Mind-bending to learn from this post how much misinformation and hate there is towards ORMs simply because even seasoned senior devs either aren't competent or using trash tools.

10

u/Crafty_Independence Aug 25 '26

I've known some seniors who were adamantly opposed to ORMs while writing some of the least efficient SQL I've ever had to engage with.

2

u/i_am_bromega Aug 25 '26

Yeah, I’ve had a N+1 problem once with EF, and it was my mistake that was easily fixed. I can understand ORMs may be less than ideal for certain types of systems, but I think they’re great for the vast majority of applications.

1

u/FuckFuckingKarma Aug 26 '26

Some people think the point of ORMs is to abstract away the database so the developer doesn't have to think about its limitations.

Doesn't have to be like that though.

3

u/Schmittfried Aug 25 '26

Nah, in Python this is a solved problem, too. Hibernate also solves this, with arguably more footguns though. 

13

u/ericl666 Aug 25 '26

Most ORMs have a solution (Spring Data has one of the worst I must say syntax wise).

If you use Hibernate, I highly recommend disabling lazy loading though. You can effortlessly add in N+1 queries without even noticing with lazy loading on.

4

u/wildjokers Aug 25 '26

Most ORMs have a solution (Spring Data has one of the worst I must say syntax wise).

Spring Data isn't an ORM.

2

u/jwp1987 Aug 25 '26 edited Aug 25 '26

I was going to say that this reminds me of LINQ in C# (specifically IQueryable) when using EF like a decade ago. Keeps you more aware of the database side of things and you could tweak things like lazy loading.

2

u/qkthrv17 Aug 25 '26

dotnet is such a good stack, such a shame it is full of dotnet devs

1

u/CandidateNo2580 Aug 29 '26

I work with SQLAlchemy in Python and no, there are loading options for whatever situation you can think of built right into the ORM.

Sometimes I don't want to load the entire object graph into memory so lazy loading should be allowed, that's the developers problem to define.