4 ms·

Something interesting to consider with N+1 queries, these warnings only really apply to remote database servers, as in not on the same machine. If you are usin

Something interesting to consider with N+1 queries, these warnings only really apply to remote database servers, as in not on the same machine. If you are using SQLite, or another in process database, N+1 isn't an issue at all. So with the increased use of SQLite as an "edge" database it's something to consider. "Many Small Queries Are Efficient In SQLite": https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html

n_e 4y ago
Although the problem will be a lot less severe than with remote servers, this is still sub-optimal: - the data passed from one query to the next still needs to move from the database process to the service process and back - the queries will always be executed in the order they are in the code, denying the optimizer the opportunity to execute the full query in the best order
- jerf 4y ago
  There is still also non-zero overhead associated with making queries in general, in both the querying and query-answering process. The ceiling of the range where you can get away with this without user-visible performance impact will be much higher, and the relative performance difference may be smaller, but in general fewer queries for the same data will still be better in general. Even with an in-process DB, you're still essentially making a sort of context switch.
  - eatonphil 4y ago
    Piling on about overhead (and SQLite), many high-level languages take some hit for using an FFI. So you're still incentivized to avoid tons of SQLite calls. https://github.com/dyu/ffi-overhead https://github.com/dyu/ffi-overhead
- Arch-TK 4y ago
  With SQLite there is no "database process" unless you are explicitly only using a dedicated process to make the queries through which isn't necessary with SQLite anyway. That being said, the problem here is not whether N+1 is a problem or not, but rather if, given the immense amount of unnecessary complexity that using an ORM brings, it is appropriate to use an ORM.
- davnicwil 4y ago
  Sub optimal in one regard, but if segmenting queries makes for simpler, easier to read and easier to debug code, then you're optimising dev time. Often this is the right tradeoff to make.
drowsspa 4y ago
You're still trading O(1) by O(N)... SQLite still has to read from the disk, it's still multiple orders of magnitude more expensive than a memory lookup.
- hinkley 4y ago
  On the one hand the individual reads will result in less blocking of write operations. On the other the read consistency behavior of the system changes by interlacing writes into the middle of your query.
- hgsgm 4y ago
  You can cache the database or part of it in memory.
  - drowsspa 4y ago
    Honestly, these workarounds to avoid doing a better architecture or to mask algorithmic complexity just come back around you to byte you.
    - ilyt 4y ago
      The key method here is not being in the project when the consequences arrive
kawsper 4y ago
It's out of fashion today, but N+1 queries are also useful (and preferred) if you're doing russian doll caching which were popular at Basecamp. It lets you only query and cache the resources that have changed, not the whole dataset: https://blog.appsignal.com/2018/04/03/russian-doll-caching-in-rails.html https://blog.appsignal.com/2018/04/03/russian-doll-caching-i...
- hinkley 4y ago
  I've never set out to implement russian doll caching, so I could be talking out of my butt, but I've worked on several projects that arrived at RDC by just throwing more and more caches at everything, and the fatal flaw there is that nobody ever goes back and determines if Cache C is undermining the effectiveness of Cache A, or if Cache C obsoletes Cache A, and should have been presented as a replacement instead of a supplement. Each cache is another failure point, and if you go down the caching rabbit hole then the appropriateness of each cache is a question that is never definitively answered. The answer changes with new features, every time your cluster grows, or shrinks, or upgrades to new instance types. It becomes a tax on your project that is either paid as you go or all at once when your complacency results in a production issue, or magnifies one.
  - kawsper 4y ago
    Basecamp/37signals also wrote about their use of the strategy here: https://signalvnoise.com/posts/3690-the-performance-impact-of-russian-doll-caching https://signalvnoise.com/posts/3690-the-performance-impact-o... I only know of them to be using it extensively, it's from 2013, so maybe things have changed :)
- JamesSwift 4y ago
  People still use russian doll caching, but its a bit of a pain honestly due to having to make sure to call `touch` in all the appropriate places, which is surprisingly a lot of places. Its very easy to forget to do so and end up with stale caches.
lowercased 4y ago
It's not a problem until it is. I was brought in to a problem to help with a migration gone south. Main dashboard page was taking 'too long'. A system had been acquired, then 'moved' to the 'new cloud architecture'. The main dashboard code made an assumption that all db queries were 'localhost' but after it was moved, the db engine was across a network (a google MySQL database and app code on GCE instance, IIRC). The dashboard was making around 8000 queries. Yes, that's sort of overwhelmingly bad. When they were all 'local', most were a fraction of a millisecond. Dashboard load time was typically ~1 second or so. Going across a network, this ballooned to 20-30 seconds (again IIRC) - some clients had load times of more than a minute. Each db connection was 2-3ms, and the code was so bad that each section of code was making its own connections - single screen might make a hundred or more database connections. A few weeks of code tracing, rewriting and lots of data caching, I got dashboard down to 5-8 seconds for most clients (who were still angry about it). To be clear, the entire process of acquiring/purchasing this system. Very little due diligence was done on company A's part. Company A bought company B, and company B staff was all let go. In any event... I'm a fan of db/app talking on 'localhost', but you can't always assume that will always be the architecture, and you should still aim to be aware of 'too many queries' and guard against when you can.
ilyt 4y ago
The only reason to ignore that is if you hate the future you that will have to deal with it

n_e 4y ago

Although the problem will be a lot less severe than with remote servers, this is still sub-optimal: - the data passed from one query to the next still needs to move from the database process to the service process and back - the queries will always be executed in the order they are in the code, denying the optimizer the opportunity to execute the full query in the best order

jerf 4y ago
There is still also non-zero overhead associated with making queries in general, in both the querying and query-answering process. The ceiling of the range where you can get away with this without user-visible performance impact will be much higher, and the relative performance difference may be smaller, but in general fewer queries for the same data will still be better in general. Even with an in-process DB, you're still essentially making a sort of context switch.
- eatonphil 4y ago
  Piling on about overhead (and SQLite), many high-level languages take some hit for using an FFI. So you're still incentivized to avoid tons of SQLite calls. https://github.com/dyu/ffi-overhead https://github.com/dyu/ffi-overhead
Arch-TK 4y ago
With SQLite there is no "database process" unless you are explicitly only using a dedicated process to make the queries through which isn't necessary with SQLite anyway. That being said, the problem here is not whether N+1 is a problem or not, but rather if, given the immense amount of unnecessary complexity that using an ORM brings, it is appropriate to use an ORM.
davnicwil 4y ago
Sub optimal in one regard, but if segmenting queries makes for simpler, easier to read and easier to debug code, then you're optimising dev time. Often this is the right tradeoff to make.

drowsspa 4y ago

You're still trading O(1) by O(N)... SQLite still has to read from the disk, it's still multiple orders of magnitude more expensive than a memory lookup.

hinkley 4y ago
On the one hand the individual reads will result in less blocking of write operations. On the other the read consistency behavior of the system changes by interlacing writes into the middle of your query.
hgsgm 4y ago
You can cache the database or part of it in memory.
- drowsspa 4y ago
  Honestly, these workarounds to avoid doing a better architecture or to mask algorithmic complexity just come back around you to byte you.
  - ilyt 4y ago
    The key method here is not being in the project when the consequences arrive

kawsper 4y ago

It's out of fashion today, but N+1 queries are also useful (and preferred) if you're doing russian doll caching which were popular at Basecamp. It lets you only query and cache the resources that have changed, not the whole dataset: https://blog.appsignal.com/2018/04/03/russian-doll-caching-in-rails.html https://blog.appsignal.com/2018/04/03/russian-doll-caching-i...

hinkley 4y ago
I've never set out to implement russian doll caching, so I could be talking out of my butt, but I've worked on several projects that arrived at RDC by just throwing more and more caches at everything, and the fatal flaw there is that nobody ever goes back and determines if Cache C is undermining the effectiveness of Cache A, or if Cache C obsoletes Cache A, and should have been presented as a replacement instead of a supplement. Each cache is another failure point, and if you go down the caching rabbit hole then the appropriateness of each cache is a question that is never definitively answered. The answer changes with new features, every time your cluster grows, or shrinks, or upgrades to new instance types. It becomes a tax on your project that is either paid as you go or all at once when your complacency results in a production issue, or magnifies one.
- kawsper 4y ago
  Basecamp/37signals also wrote about their use of the strategy here: https://signalvnoise.com/posts/3690-the-performance-impact-of-russian-doll-caching https://signalvnoise.com/posts/3690-the-performance-impact-o... I only know of them to be using it extensively, it's from 2013, so maybe things have changed :)
JamesSwift 4y ago
People still use russian doll caching, but its a bit of a pain honestly due to having to make sure to call `touch` in all the appropriate places, which is surprisingly a lot of places. Its very easy to forget to do so and end up with stale caches.

lowercased 4y ago

It's not a problem until it is. I was brought in to a problem to help with a migration gone south. Main dashboard page was taking 'too long'. A system had been acquired, then 'moved' to the 'new cloud architecture'. The main dashboard code made an assumption that all db queries were 'localhost' but after it was moved, the db engine was across a network (a google MySQL database and app code on GCE instance, IIRC). The dashboard was making around 8000 queries. Yes, that's sort of overwhelmingly bad. When they were all 'local', most were a fraction of a millisecond. Dashboard load time was typically ~1 second or so. Going across a network, this ballooned to 20-30 seconds (again IIRC) - some clients had load times of more than a minute. Each db connection was 2-3ms, and the code was so bad that each section of code was making its own connections - single screen might make a hundred or more database connections. A few weeks of code tracing, rewriting and lots of data caching, I got dashboard down to 5-8 seconds for most clients (who were still angry about it). To be clear, the entire process of acquiring/purchasing this system. Very little due diligence was done on company A's part. Company A bought company B, and company B staff was all let go. In any event... I'm a fan of db/app talking on 'localhost', but you can't always assume that will always be the architecture, and you should still aim to be aware of 'too many queries' and guard against when you can.

ilyt 4y ago

The only reason to ignore that is if you hate the future you that will have to deal with it