3 ms·

I haven't heard the phrase "N+1 problem" before -- is this a Ruby-on-Rails-ism or do other communities use it too?

detaro 3y ago
General term, especially common around ORMs
brightball 3y ago
Simple example: You're using an ORM to query a record and as you're looping through the results you need to reference something in an association. You get one query that returns 50 results, then loop through those 50 results and trigger a one off query for every result in the loop. Now you're executing 51 queries instead of the original one. Now imagine you go another level deep or have multiple results from each of those sub queries to loop through. It can get out of control. In Rails and a lot of ORM's you can typically solve these by eager loading the nested associations with very little effort. The rest of the code will work the same but behind the scenes only 1 additional query will be triggered to fetch the records from the association.
jarofgreen 3y ago
It's a general ORM issue. I once took a Python API with SQL Alchemy and took 30% of the speed by adding an eager load instruction to a query. Python profiling showed a lot of time in the ORM & database, then turning on SQL logging made the issue obvious. Still feels like it should be called the "1 + N" problem tho. The 1 query happens first, then the N queries afterwards.
ufmace 3y ago
It's a general database access anti-pattern. It happens when you make one simple query to get a list of stuff. Then for every row, you fire off another query to get more details about that item. Instead of this, a single properly-designed query could have retrieved everything you need, and nothing you didn't. This can be highly detrimental to both application and database performance, especially if the number of initial results gets long or you have another layer of query loops after the first one. ORMs are notorious for making it easy to write this anti-pattern without noticing, though you can do it in raw SQL too. ORMs usually also have various helpers for eager loading of associations to try and prevent it. In raw SQL, it's on you to notice that you're doing it and write the correct query.

General term, especially common around ORMs

Simple example: You're using an ORM to query a record and as you're looping through the results you need to reference something in an association. You get one query that returns 50 results, then loop through those 50 results and trigger a one off query for every result in the loop. Now you're executing 51 queries instead of the original one. Now imagine you go another level deep or have multiple results from each of those sub queries to loop through. It can get out of control. In Rails and a lot of ORM's you can typically solve these by eager loading the nested associations with very little effort. The rest of the code will work the same but behind the scenes only 1 additional query will be triggered to fetch the records from the association.

jarofgreen 3y ago

It's a general ORM issue. I once took a Python API with SQL Alchemy and took 30% of the speed by adding an eager load instruction to a query. Python profiling showed a lot of time in the ORM & database, then turning on SQL logging made the issue obvious. Still feels like it should be called the "1 + N" problem tho. The 1 query happens first, then the N queries afterwards.

ufmace 3y ago

It's a general database access anti-pattern. It happens when you make one simple query to get a list of stuff. Then for every row, you fire off another query to get more details about that item. Instead of this, a single properly-designed query could have retrieved everything you need, and nothing you didn't. This can be highly detrimental to both application and database performance, especially if the number of initial results gets long or you have another layer of query loops after the first one. ORMs are notorious for making it easy to write this anti-pattern without noticing, though you can do it in raw SQL too. ORMs usually also have various helpers for eager loading of associations to try and prevent it. In raw SQL, it's on you to notice that you're doing it and write the correct query.