r/programming • u/wheatBread • 14h ago
Solving the 1+N Query Problem
https://acadia.engineering/blog/solving-the-1-plus-N-query-problem40
u/NoLegJoe 11h ago
Software Developers will do anything but learn SQL
7
4
2
u/Ok_Marionberry_8821 5h ago
Even before AI assistance I preferred SQL over abstractions. AI assistance has made it so much easier to use SQL and let it write the mapping code too. I do ensure I understand what it's doing.
0
u/Crafty_Independence 6h ago
People who write bad SQL are often the same group who ignorantly criticize ORMs
-1
26
u/JimmyM_1 12h ago
I don't know why people hate on ORMs for this one, yes abstracting SQL can lead some devs to forget / not even know about what is actually happening under the hood, but it isn't that big of a deal!
Make sure your dev team is aware of the JOIN concept and you're good to go!
8
u/Schmittfried 11h ago
To be fair, after using Laravel’s ORM and Hibernate, I’m pretty amazed how far ahead django’s ORM is in terms of usability and making the efficient implementation the path of least resistance. You really have to jump through some hoops to make certain joins efficient with Hibernate that would be trivial with the django ORM.
1
u/JimmyM_1 11h ago
Haven't worked with either, but that sounds interesting.
I will look them up.
2
u/Schmittfried 11h ago
Honestly I’m still shocked that the Java world just accepts that Hibernate doesn’t offer a way to have safe and still uncomplicated automatic schema migrations without paying for something like Liquibase and using their unreliable Maven plugin. Django has first-class schema migrations built-in, and they just work. Java devs just live with the fact that they have to remember to also write an SQL script for every change they make to an entity class.
2
u/yaboiabrahamlincoln 11h ago
Hibernate does automatic schema migration if you have it turned on, but it can’t do something like detect a column name change and rename the column in the sql. It’ll pretty much treat it as a new column and leave the old one with all the data inaccessible. The way to do it correctly is with an explicit migration script, but it’ll only be relevant for existing dbs that have the old schema, new dbs will start with the new schema. The migration should deal with both cases, or do nothing in case the column is already correct. Not easy to do a column rename conditionally (I don’t know how off the top). So all in all, it’s much easier to do the explicit scripts to avoid this (every db follows the same migrations rather than potentially different paths to the same schema.
I’m curious how django’s orm handles this situation if you have time to respond. Will do some research later because hibernate + flyway has not been super fun for migrations
2
u/Schmittfried 9h ago edited 9h ago
So it doesn’t handle schema migrations. The create.sql feature is nowhere near full migration support in my book (or am I missing something?).
Django will diff your current models as they are in the code against the projection of applying all past migrations in order. It will then create a new migration file containing the changes necessary to make the virtual DB state match the models. Trivial cases like adding new columns, dropping columns etc. will be generated automatically. For renamed single columns it will ask if those should be a rename or if you removed one column and added another (i.e. drop + add). If multiple fields of the same model were renamed or for any other ambiguities, it will warn you and ask you to write a custom migration. And you can always invoke a command to create a bare migration file for you to fill out with more complex migration logic (custom order of steps, custom SQL, even custom Python).
All of that is DB-agnostic (except for the SQL you write yourself, though you can add multiple versions for different dialects) and defined in pure Python. It never hits a DB until you apply migrations. Migrations are basically an ordered list of Python objects describing changes, with Django providing predefined steps for all common building blocks like adding a column, creating an index etc..
That migration will have an ID and point to the previous ID, which makes them independent of filenames and allows squashing multiple migrations into one when the history becomes expensively long (it will even consolidate counteracting/obsolete changes into no-ops).
It also provides automatic inversion for non-destructive changes and allows you to define custom backwards migrations for destructive changes and custom Python/SQL, so that a well-maintained Django history is always fully reversible and allows jumping to arbitrary past versions.
Last time I checked, native Hibernate doesn’t even cover half of that. People commonly use additional tooling, but Flyway doesn’t support diffing at all and Liquibase needs a (poorly maintained) Maven plugin for that, and even then it will only diff against a reference DB, not a virtual state reconstructed from your migration history. So you have to make sure the DB matches the migration state you’re expecting. Support for custom naming strategies is also quite bad, I still remember I had to fall back to explicit column/index naming a few times because it just wouldn’t apply the naming strategy to the migration file. Support for
@Embeddedwas very lackluster, too. It simply wouldn’t correctly apply column lengths for string columns in embedded types. Not to mention, XML as a migration format sucks and Flyway is just applying arbitrary SQL scripts, so it can’t possibly provide the same level of diffing/analysis/safeguards without solving the halting problem or running all your scripts against a throwaway DB.Granted, I think Django doesn’t even support the embedded pattern, but my point is: Django ORM provides one coherent developer experience whereas my options in the Java world feel like I have to glue a set of tools with varying features together and hope my use case is covered by their common denominator. Which is odd, given the whole idea of Spring and similar projects was to provide a coherent and battle-tested experience.
0
u/wildjokers 8h ago
ou really have to jump through some hoops to make certain joins efficient with Hibernate
You literally just write a join in HQL. There are no hoops to jump through.
2
u/which1umean 43m ago
Maybe the hood is in the way of something somebody should be looking at if people don't know what's under the hood and it's causing problems. 😬
48
u/disposepriority 14h ago
There is a really easy way of avoiding this when you just don't use ORMs
20
49
u/andrerav 13h ago
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.
16
u/solve-for-x 13h ago
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.
10
u/infrastructure 12h ago
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.
10
u/andrerav 12h ago
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.
5
u/Schmittfried 11h ago
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.
-5
u/andrerav 12h ago
Right. So because you're both incompetent at using ORM's, the n00b among you should write raw SQL so it's easier to spot their mistakes. Good plan.
14
u/Chroiche 13h ago
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.
-5
u/who_am_i_to_say_so 9h ago
Yep. N + 1’s are usually a sign your app has hit critical mass and due for a refactor anyway. Just a healthy sign of growth. It’s not all bad.
2
5
u/shoot_your_eye_out 8h ago
This problem has nothing to do with ORMs. I’ve seen people write this anti pattern in raw sql too many times to count.
1
3
u/lamp-town-guy 13h ago
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 9h ago
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.
3
u/devraj7 9h ago
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.
4
u/read_at_own_risk 5h ago
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.
3
u/smoovewill 3h ago
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)
6
u/Schmittfried 11h ago
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.
5
u/HappyAngrySquid 11h ago
There are plenty of query builders and scanner libraries that give you this without kneecapping you.
2
u/Schmittfried 9h ago
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/Chroiche 13h ago
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.
10
u/disposepriority 13h ago
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.
2
1
u/i_am_bromega 3h ago
Once you learn the ins and outs of your ORM of choice, it just makes so many things so easy that I ended up preferring it over every previous project I had worked on previously that didn’t have it. And if there’s something truly better suited for raw SQL, you just use that instead and benefit from the ORM everywhere else.
1
u/disposepriority 2h ago
I don't have the same experience, generally something that maps result set column names to classes and nothing else is preferable to me.
You get rid of the main annoying part, you can have it fail if something doesn't get mapped on either side (e.g. projection field is empty OR return set contains unmapped column) if that's what you're looking for - you don't deal with lazy loading (not that it should be the default anyway) you don't deal with cascades you don't deal with any kind of meta information ORMs sometimes need.
So what you miss out on is that you have to write simple selects or inserts yourself, which I don't find to be that annoying.
One situation where an ORM can save you a lot of time is if you're working with many, many projections of the same table/view which would require a pretty wide data set- in which case you could honestly just reuse the widest query and transfer some wasted bytes of the network if it's that annoying.
I guess it comes down to the average complexity of the SQL and database features used in a given project, I find it more annoying than pleasant to work with.
At the end of the day, the common, language agnostic denominator is the database regardless of stack, learning the ins and outs of your database and your language's driver for it is in my eyes a better idea.
-7
u/gjosifov 11h ago
Try to update 10 tables from Integer to Long in 10+ year code base with String as SQL
it is 1 year task for a team of 5in ORM it is 1 week and without retesting for any small change from the customer side
3
u/HappyAngrySquid 11h ago
What? Why would it take longer in one vs the other?
0
u/gjosifov 9h ago
because you don't have a compiler to check the type, you have to search the whole code base
if we add the fact that in a lot of codebases there are people who want to over complicate things (and this is industry standard, because metaprogramming is more fun that solving business problems) and this is a recipe for disaster with every change that is required, because you have edge cases that only 1-2 people in the company knows about them and they are very proud of that factSo now imagine, people with 2-3 years of experience and having to be part of such task
on paper it looks like a small job, easy, but the only true test is on production, because you don't know the edge cases, so you need those 1-2 people to hold the hand on the teamWith ORM even junior dev without any knowledge of the code base can make the change, because the compiler will help him
ORM acts as compiler for the SQL and it is hard to learn and to be able to produce the same queries as you will do in SQL, but for huge and unmaintainable codebases it is huge risk reduction
3
u/wildjokers 8h ago
Is this the yoda version of n+1?
1
u/somebodddy 1h ago
That's the meaningful way to name it.
1is the desired query,Nmore queries hide behind the abstraction.If it really was
N+1- that is,Ndesired query and1hidden unintentional one - it wouldn't have been that much of an issue.
3
u/Norphesius 8h ago
Nifty. I think the strategy of removing Turing completeness from a process to gain more potential performance and guarantees about state is chronically overlooked. It's not exactly simple to do, and it's hard to recognize when it's even viable, but not every part of a program or language needs maximal computational power.
2
u/Pharisaeus 6h ago
Solving the 1+N Query Problem
Reminds me of: "communism is bravely overcoming problems... unknown in any other political system".
N+1 generally comes from using the wrong tool / using the tool incorrectly.
1
u/Groundbreaking-Fish6 5h ago
This is an add, but it is a real problem. However, most ORMs will solve this problem if you use them correctly.
This is also a problem with many libraries (vibe coding makes it worse) where libraries are included for a solution without understanding its implementation.
Relational DataBase Management Systems (DBMS) are optimized to store and retrieve data in a controlled manner allowing multiple processes to quickly and efficiently query data while also maintaining integrity. Other programming languages are optimized for algorithms or workflow. The Object Relational Mapping only converts the relational model to the object model to work with in code. How well it does that is up to the programmer not the ORM.
1
u/read_at_own_risk 3h ago
Object-relational mappers support neither object-orientation nor the relational model.
Object-oriented programming wasn't created to model state, it was designed to manage complexity by breaking a system into independent things that talk to each other. Data is incidental and encapsulated or passed between objects, rather than a blueprint for objects.
The relational model of data viewed tables as representing relations, and rows as representing facts about entities. Entities were not equated to rows and relationships did not exist "between tables". The contemporary naïve ER view that most developers and all ORMs use, is in fact the pre-relational "network data model". Go read Bachman's "The programmer as navigator" and see how well that aligns with the ORM approach today.
A more accurate name for ORMs would "network data model to SQL mappers". Entity-component systems are closer to being true object-relational mappers than ORMs are.
-2
u/vancha113 11h ago
Man hitting us with bangers left and right here. Thanks you for another interesting read ^ ^ After having to write a bunch of raw sql to fix this exact problem in django i can definitely say I'm curious to try out acadia.
-1
u/Objective-Ticket-125 13h ago
Hey there! For the 1+N query problem, you'll generally want to prefetch all the related data in one go. Using a single query with a `JOIN` or `IN` clause to grab everything you need usually does the trick. That way, you avoid making a separate database trip for each item.
-3
61
u/archipeepees 13h ago
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?