I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
I think it’s underblown. If more people were properly horrified about how much more needless work it causes the DB server, the network, and the client to do, they’d burn it with fire.
In my experience it’s the biggest culprit behind random system fallovers that “work on my machine”. Well sure. Your test page load only resulted in 8 round trips to the server with a hidden O(n^2) in-process merge. The same page on the fully populated prod server required 137 round trips, and it still fails under load even though you can’t physically fit more RAM in the thing.
Agree the DB side is overstated. These particularly suck though in ruby on multithreaded servers (eg puma), where N+1s are constantly giving control of the GVL to another potentially greedy thread. I’m sure python multithreaded servers will suffer similarly.
Oh it definitely isn't. Data volume may be small, but latency will kill you, especially if your database server isn't on the same machine as your application. If you fetch an entity A with a one-to-many to entity B, with a typical size of 100 B's for every A, you are looking at 101 separate queries if you access any relations on B, which assuming 1ms of network latency give you a lowerbound of 100ms for an operation that may have taken well below 10ms in total had you fetched in one go.
That's also 101 entities. That's a half a thousand values if A and B have ~5 attributes. That's too much data. No human can make use of it and no application should offer it. Good ones won't. They'll impose limits and when they do they'll naturally also be imposing a limit on the size of the N+1 problem. That's why I say that in my opinion, it's overblown. You're welcome to form your own opinion, of course.
Happy to name a concrete use case: showing a manifest of containers to load/unload from a ship. Search needs to happen client-side for many reasons, ballpark figures may be 1000 loads, 1000 unloads, the info about the shipvisit, and a stowage plan of 1000 records loading/unloading (separate from the manifest). Give you 4001 individual entities, clocking in at at least 20 attributes per entity (container number, type, port of loading/discharge, cargo description, weight etc)
N+1 just isn't cutting it for that use case, because while a manifest may be paginated, you pretty much just need your entire stowage plan in memory to do any useful operation on it.
If I'm reading you correctly you're saying there's an industrial grade application with a UI that routinely shows up to 80000 attributes to a user, or at least needs that information for client-side processing. Setting aside whether that's justified for this particular use case, do you think that's a common use case in let's say consumer grade web and mobile applications?
No, probably not, but SQL and ORMs aren’t used just for consumer grade applications ;)
I’m sure N+1 is not a big problem in those use cases, but saying that thereforr the problem doesn’t exist is overgeneralizing a bit. B2B is not a small market.
I don't believe I said the N+1 problem doesn't exist. I said that I believe it's "overblown". I'll grant that's vague, and I can be more precise, but one thing I mean by "overblown" is that it's not necessarily a big problem in all cases, even if it's sometimes a problem in some cases. I definitely do not mean that it's never a big problem in any cases.
> The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
Isn't that the point? Repeated network calls will cause latency even if the data is minimal; the total amount of data returned will always be the same, so the "N" extra network calls are pure overhead. For applications where the amount of data is usually small, network calls will likely be the bottleneck.
Again, the number of network calls will scale with the total data payload of the overall request, but the requested total data payload should never be large. It's effectively capped. Therefore, the number of network calls is effectively capped. The N+1 SQL call multiplying factor may be 2, or 10, but it shouldn't be 100 or 1000. That's what I mean when I say I think the problem is overblown. It's not that it isn't a problem--for some people--but that it's not a problem that can grow without bounds.
Put another way, while the N+1 problem scales the number of SQL calls exponentially, it also scales the data payload the same way. Large payloads shouldn't occur and won't occur if we take steps to ensure that they don't. If we do, then we're also pretty much guaranteeing large batches of SQL statements will likewise be limited.
