News for hackers. Stuff that matters.
Re:Understanding N + 1 queries problem by samwillis on Monday January 02, 2023 @02:18PM (#34222340)
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

Re:Understanding N + 1 queries problem by n_e on Monday January 02, 2023 @02:43PM (#34222663)
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

Re:Understanding N + 1 queries problem by jerf on Monday January 02, 2023 @02:52PM (#34222775)
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.

Re:Understanding N + 1 queries problem by eatonphil on Monday January 02, 2023 @02:56PM (#34222811)
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

Re:Understanding N + 1 queries problem by Arch-TK on Monday January 02, 2023 @03:15PM (#34223051)
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.

Re:Understanding N + 1 queries problem by davnicwil on Tuesday January 03, 2023 @06:19AM (#34230176)
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.
Re:Understanding N + 1 queries problem by drowsspa on Monday January 02, 2023 @03:21PM (#34223121)
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.
Re:Understanding N + 1 queries problem by hinkley on Monday January 02, 2023 @04:29PM (#34223925)
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.
Re:Understanding N + 1 queries problem by hgsgm on Monday January 02, 2023 @06:52PM (#34225452)
You can cache the database or part of it in memory.
Re:Understanding N + 1 queries problem by drowsspa on Monday January 02, 2023 @08:01PM (#34226115)
Honestly, these workarounds to avoid doing a better architecture or to mask algorithmic complexity just come back around you to byte you.
Re:Understanding N + 1 queries problem by ilyt on Tuesday January 03, 2023 @09:53AM (#34232074)
The key method here is not being in the project when the consequences arrive
Re:Understanding N + 1 queries problem by kawsper on Monday January 02, 2023 @04:11PM (#34223729)
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-i...

Re:Understanding N + 1 queries problem by hinkley on Monday January 02, 2023 @04:26PM (#34223892)
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.
Re:Understanding N + 1 queries problem by kawsper on Monday January 02, 2023 @06:42PM (#34225362)
Basecamp/37signals also wrote about their use of the strategy here: 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 :)

Re:Understanding N + 1 queries problem by JamesSwift on Tuesday January 03, 2023 @09:46AM (#34231969)
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.
Re:Understanding N + 1 queries problem by lowercased on Monday January 02, 2023 @04:27PM (#34223901)
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.

Re:Understanding N + 1 queries problem by ilyt on Tuesday January 03, 2023 @09:52AM (#34232067)
The only reason to ignore that is if you hate the future you that will have to deal with it