- 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
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.
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.
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...
I only know of them to be using it extensively, it's from 2013, so maybe things have changed :)
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.
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