5 ms·

Isn't "n + 1" typically a degenerate case of ORM rather than a relational thing? The whole point of rdbms is you can do a join instead of iterated lookups.

pphysch 4y ago
Yeah most N+1 issues can be mitigated by optimizing the queries. You definitely don't need an ORM to get to N+1 hell, a for-loop in vanilla PHP will do.
- mgkimsal 4y ago
  A for-loop in vanilla anything will do. Nothing special about PHP. Ruby, Python, Java, etc...
  - pphysch 4y ago
    Of course. PHP is the one you will mainly encounter in the wilderness, though.
    - 0x457 4y ago
      Most common place I saw N+1 is ActiveRecord (Ruby on Rails). Because by default, no relationships are loaded and the way AR "loads" stuff makes is extremely easy to create N+M queries on 5 lines of ruby. Then, those people will tell you that you run out of database before you run out of ruby...
- woooooo 4y ago
  Yeah, but you have to go out of your way to do that. If you're in touch with the SQL you're executing at all, the join is the obvious easy way to do it, it's not some arcane optimization.
  - RussianCow 4y ago
    That’s a really big “if”. In my experience, most application engineers aren’t very good at SQL because they don’t have to be; most apps just don't ever reach a scale where it matters, and something like Rails or Django with N+1 queries all over the place performs just fine.
    - woooooo 4y ago
      Hence my 2 decade losing battle against ORM at every place I've worked :) I'm, like, 1 for 10 maybe.
  - tyingq 4y ago
    You do see it often in things like canned ecommerce software. They have this ecosystem that encourages you to add widgets to category listings, product listings, etc, that all create queries keying off the page context. Where widgets might be "similar items", "people that bought x, bought y", "recent purchases of this widget", "best sellers in this category" and so on.
dspillett 4y ago
You see it in direct code too, from new or naive coders (the good old "it was fast enough on my test data…") or when proof-of-concept code "accidentally" escapes into production. Even within the database you'll sometimes find examples: stored procedures looping over a cursor and running queries or other statements in each iteration. This doesn't have the same latency implications as N+1 requests over a network, but still does add significant time per step compared to a set based equivalent. If you think about it, the database will sometimes do this itself when your database is not optimally structured & indexed for your queries (in SQL Server, look at "number of executions" in query plans).
- nicoburns 4y ago
  > You see it in direct code too, from new or naive coders Many moons ago in one of my first coding projects I not only used a separate query for each record, but a separate database connection too. In addition to this, I didn't use a loop to generate these queries, I copy and pasted the connection setup and query code for each record, leading to a 22k code file just for this single web page. There were probably on the order of 1000 records (they represented premier league football players), and amazingly this code was fast enough (took a couple of seconds to load - which wasn't so unusual back in 2007) and worked well enough that it actually had ~100 real users (not bad a 14 year old's side project).
randomdata 4y ago
It is a result of representing application state as complex entity structures. I am not sure I would call it degenerate. n+1 would be quite natural if relational databases were theoretically pure. It only falls down in the real world because we accept deviation from the theory to allow implementation. Joins are one possible workaround to the n+1 problem. However, they push the problem into the application to unpack, which detracts from having a nice high level abstraction in the database for fetching data. The many queries of n+1 would be ideal if you had an ideal database. You are querying on behalf of many different entities so many different queries is conceptually sound. But since we don't have ideal databases... That's not to say that joins don't have their place. In the relational model, joins are essential. But the n+1 problem happens when you are not working in the relational model.

pphysch 4y ago

Yeah most N+1 issues can be mitigated by optimizing the queries. You definitely don't need an ORM to get to N+1 hell, a for-loop in vanilla PHP will do.

mgkimsal 4y ago
A for-loop in vanilla anything will do. Nothing special about PHP. Ruby, Python, Java, etc...
- pphysch 4y ago
  Of course. PHP is the one you will mainly encounter in the wilderness, though.
  - 0x457 4y ago
    Most common place I saw N+1 is ActiveRecord (Ruby on Rails). Because by default, no relationships are loaded and the way AR "loads" stuff makes is extremely easy to create N+M queries on 5 lines of ruby. Then, those people will tell you that you run out of database before you run out of ruby...
woooooo 4y ago
Yeah, but you have to go out of your way to do that. If you're in touch with the SQL you're executing at all, the join is the obvious easy way to do it, it's not some arcane optimization.
- RussianCow 4y ago
  That’s a really big “if”. In my experience, most application engineers aren’t very good at SQL because they don’t have to be; most apps just don't ever reach a scale where it matters, and something like Rails or Django with N+1 queries all over the place performs just fine.
  - woooooo 4y ago
    Hence my 2 decade losing battle against ORM at every place I've worked :) I'm, like, 1 for 10 maybe.
- tyingq 4y ago
  You do see it often in things like canned ecommerce software. They have this ecosystem that encourages you to add widgets to category listings, product listings, etc, that all create queries keying off the page context. Where widgets might be "similar items", "people that bought x, bought y", "recent purchases of this widget", "best sellers in this category" and so on.

dspillett 4y ago

You see it in direct code too, from new or naive coders (the good old "it was fast enough on my test data…") or when proof-of-concept code "accidentally" escapes into production. Even within the database you'll sometimes find examples: stored procedures looping over a cursor and running queries or other statements in each iteration. This doesn't have the same latency implications as N+1 requests over a network, but still does add significant time per step compared to a set based equivalent. If you think about it, the database will sometimes do this itself when your database is not optimally structured & indexed for your queries (in SQL Server, look at "number of executions" in query plans).

nicoburns 4y ago
> You see it in direct code too, from new or naive coders Many moons ago in one of my first coding projects I not only used a separate query for each record, but a separate database connection too. In addition to this, I didn't use a loop to generate these queries, I copy and pasted the connection setup and query code for each record, leading to a 22k code file just for this single web page. There were probably on the order of 1000 records (they represented premier league football players), and amazingly this code was fast enough (took a couple of seconds to load - which wasn't so unusual back in 2007) and worked well enough that it actually had ~100 real users (not bad a 14 year old's side project).

randomdata 4y ago

It is a result of representing application state as complex entity structures. I am not sure I would call it degenerate. n+1 would be quite natural if relational databases were theoretically pure. It only falls down in the real world because we accept deviation from the theory to allow implementation. Joins are one possible workaround to the n+1 problem. However, they push the problem into the application to unpack, which detracts from having a nice high level abstraction in the database for fetching data. The many queries of n+1 would be ideal if you had an ideal database. You are querying on behalf of many different entities so many different queries is conceptually sound. But since we don't have ideal databases... That's not to say that joins don't have their place. In the relational model, joins are essential. But the n+1 problem happens when you are not working in the relational model.