5 ms·

What is N+1?

simonw 1y ago
The thing where your app displays 20 stories in the homepage, but for each story it runs an extra query to fetch the author, and another to fetch the tags. It's usually a big problem for database performance because each query carries additional overhead for the network round trip to the database server. SQLite queries are effectively a C function call accessing data on local disk so this is much less of an issue - there's an article about that in the SQLite docs here: https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
genpfault 1y ago
https://stackoverflow.com/questions/97197 https://stackoverflow.com/questions/97197
Scubabear68 1y ago
Object Relational Mapping (ORM) tools, which focus on mapping between code based objects and SQL tables, often suffer from what is called the N+1 problem. A naive ORM setup will often end up doing a 1 query to get a list of object it needs, and then perform N queries, one per object, usually fetching each object individually by ID or key. So for example, if you wanted to see “all TVs by Samsung” on a consumer site, it would do 1 query to figure out the set of items that match, and then if say 200 items matched, it would do 200 queries to get those individual items. ORMs are better at avoiding it these days, depending on the ORM or language, but it still can happen.
- OJFord 1y ago
  I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.
  - whizzter 1y ago
    Yep, people who think OOP is all you need will just "abstract away the database".
  - iamflimflam1 1y ago
    Very true. But ORMs did make it particularly easy to trigger N+1 selects. It used to be a very common pitfall - and often not at all obvious. You’d grab a collection of objects from the ORM, process them in a loop, and everything looked fine because the objects were already rehydrated in memory. Then later, someone would access a property on a child object inside that loop. What looked like a simple property access would silently trigger a database query. The kicker was that this could be far removed from any obvious database access, so the person causing the issue often had no idea they were generating dozens (or hundreds) of extra queries.
  - Spivak 1y ago
    This problem is associated with ORMs but the moment there's a get_user(id) function which does a select and you need to display a list of users someone will run it in a loop to generate the list and it will look like it's working until the user list gets long. I really wish there was a way to compose SQL so you can actually write the dumb/obvious thing and it will run a single query. I talked with a dev once who seemed to have the beginnings of a system that could do this. It leveraged async and put composable queryish objects into a queue and kept track of what what callers needed what results, merged and executed the single query, and then returned the results. Obviously far from generalizable for arbitrary queries but it did seem to work.
    - riffraff 1y ago
      I think many ORMs can solve (some of) this these days. e.g. for ActiveRecord there's ar_lazy_preloader[0] or goldiloader[1] which fix many N+1s by keeping track of a context: you load a set of User in one go, and when you do user.posts it will do a single query for all, and when you then access post.likes it will load all likes for those and so on. Or, if you get the records some other way, you add them to a shared context and then it works. Doesn't solve everything, but helps quite a bit. [0] https://github.com/DmitryTsepelev/ar_lazy_preload https://github.com/DmitryTsepelev/ar_lazy_preload [1] https://github.com/salsify/goldiloader https://github.com/salsify/goldiloader
  - skeeter2020 1y ago
    I defense of the application developer, it is very difficult to adopt set theory thinking which helps with SQL when you've never had any real education in this area, and it's tough to switch between it and the loop-oriented processing you're likely using in your application code for almost everyone. ORMs bridge this divide which is why they fall in the trap consistently. Often it's an acceptable trade-off for the value you get from the abstraction, but then you pay the price when you need to address the leak!
cbm-vic-20 1y ago
There's a common access pattern with object-relational mapping frameworks where an initial query will be used to get a list of ids, then an individual queries are emitted for each item to get the details of the items. For example, if you have a database table full of stories, and you want to see only the stories written by a certain author, it is common for a framework to have a function like stories = get_stories(query) which results in a SQL query like SELECT id FROM stories WHERE author = ? with the '?' being bound to some concrete value like "Jim". Then, the framework will be used to do something like this for id in stories { story = get_story_by_id(id) // do something with story } which results in N SQL queries with SELECT title, author, date, content FROM stories WHERE id = ? and there's your N+1
- ComputerGuru 1y ago
  This plagues (plagued?) pretty much everything to do with WordPress, from core to every theme and plugin developed.
- metadat 1y ago
  Oh yeah, the ORM thing (common side-effect with DB query abstractions) - I must not have been fully awake. Cheers and thank you for humoring me, @cbm-vic-20!
  - tehlike 1y ago
    With orms, it can be easy, but also often fixed with eager fetching too.
upmostly 1y ago
The N+1 problem basically means instead of making one efficient query, you end up making N separate queries inside a loop. For example, fetching a list of tables, then for each table fetching its columns individually — that’s N+1 queries. It works, but it’s slow. We ran into this while building, funnily enough, a database management app called DB Pro (https://dbpro.app https://dbpro.app) At first we were doing exactly that: query for all schemas, then for each schema query its tables, and then for each table query its columns. On a database with hundreds of tables it took ~3.8s. We fixed it by flipping the approach: query all the schemas, then all the tables, then all the columns in one go, and join them in memory. That dropped the load time to ~180ms. N+1 is one of those things you only really “get” when you hit it in practice.

What is N+1?

The thing where your app displays 20 stories in the homepage, but for each story it runs an extra query to fetch the author, and another to fetch the tags. It's usually a big problem for database performance because each query carries additional overhead for the network round trip to the database server. SQLite queries are effectively a C function call accessing data on local disk so this is much less of an issue - there's an article about that in the SQLite docs here: https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html

genpfault 1y ago

https://stackoverflow.com/questions/97197 https://stackoverflow.com/questions/97197

Scubabear68 1y ago

Object Relational Mapping (ORM) tools, which focus on mapping between code based objects and SQL tables, often suffer from what is called the N+1 problem. A naive ORM setup will often end up doing a 1 query to get a list of object it needs, and then perform N queries, one per object, usually fetching each object individually by ID or key. So for example, if you wanted to see “all TVs by Samsung” on a consumer site, it would do 1 query to figure out the set of items that match, and then if say 200 items matched, it would do 200 queries to get those individual items. ORMs are better at avoiding it these days, depending on the ORM or language, but it still can happen.

OJFord 1y ago
I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.
- whizzter 1y ago
  Yep, people who think OOP is all you need will just "abstract away the database".
- iamflimflam1 1y ago
  Very true. But ORMs did make it particularly easy to trigger N+1 selects. It used to be a very common pitfall - and often not at all obvious. You’d grab a collection of objects from the ORM, process them in a loop, and everything looked fine because the objects were already rehydrated in memory. Then later, someone would access a property on a child object inside that loop. What looked like a simple property access would silently trigger a database query. The kicker was that this could be far removed from any obvious database access, so the person causing the issue often had no idea they were generating dozens (or hundreds) of extra queries.
- Spivak 1y ago
  This problem is associated with ORMs but the moment there's a get_user(id) function which does a select and you need to display a list of users someone will run it in a loop to generate the list and it will look like it's working until the user list gets long. I really wish there was a way to compose SQL so you can actually write the dumb/obvious thing and it will run a single query. I talked with a dev once who seemed to have the beginnings of a system that could do this. It leveraged async and put composable queryish objects into a queue and kept track of what what callers needed what results, merged and executed the single query, and then returned the results. Obviously far from generalizable for arbitrary queries but it did seem to work.
  - riffraff 1y ago
    I think many ORMs can solve (some of) this these days. e.g. for ActiveRecord there's ar_lazy_preloader[0] or goldiloader[1] which fix many N+1s by keeping track of a context: you load a set of User in one go, and when you do user.posts it will do a single query for all, and when you then access post.likes it will load all likes for those and so on. Or, if you get the records some other way, you add them to a shared context and then it works. Doesn't solve everything, but helps quite a bit. [0] https://github.com/DmitryTsepelev/ar_lazy_preload https://github.com/DmitryTsepelev/ar_lazy_preload [1] https://github.com/salsify/goldiloader https://github.com/salsify/goldiloader
- skeeter2020 1y ago
  I defense of the application developer, it is very difficult to adopt set theory thinking which helps with SQL when you've never had any real education in this area, and it's tough to switch between it and the loop-oriented processing you're likely using in your application code for almost everyone. ORMs bridge this divide which is why they fall in the trap consistently. Often it's an acceptable trade-off for the value you get from the abstraction, but then you pay the price when you need to address the leak!

cbm-vic-20 1y ago

There's a common access pattern with object-relational mapping frameworks where an initial query will be used to get a list of ids, then an individual queries are emitted for each item to get the details of the items. For example, if you have a database table full of stories, and you want to see only the stories written by a certain author, it is common for a framework to have a function like stories = get_stories(query) which results in a SQL query like SELECT id FROM stories WHERE author = ? with the '?' being bound to some concrete value like "Jim". Then, the framework will be used to do something like this for id in stories { story = get_story_by_id(id) // do something with story } which results in N SQL queries with SELECT title, author, date, content FROM stories WHERE id = ? and there's your N+1

ComputerGuru 1y ago
This plagues (plagued?) pretty much everything to do with WordPress, from core to every theme and plugin developed.
metadat 1y ago
Oh yeah, the ORM thing (common side-effect with DB query abstractions) - I must not have been fully awake. Cheers and thank you for humoring me, @cbm-vic-20!
- tehlike 1y ago
  With orms, it can be easy, but also often fixed with eager fetching too.

upmostly 1y ago

The N+1 problem basically means instead of making one efficient query, you end up making N separate queries inside a loop. For example, fetching a list of tables, then for each table fetching its columns individually — that’s N+1 queries. It works, but it’s slow. We ran into this while building, funnily enough, a database management app called DB Pro (https://dbpro.app https://dbpro.app) At first we were doing exactly that: query for all schemas, then for each schema query its tables, and then for each table query its columns. On a database with hundreds of tables it took ~3.8s. We fixed it by flipping the approach: query all the schemas, then all the tables, then all the columns in one go, and join them in memory. That dropped the load time to ~180ms. N+1 is one of those things you only really “get” when you hit it in practice.