I bet wordpress (or many plugins) is/are so slow exactly because of this.
ORMs will even make it easier to write more efficient queries is many cases.
I don't think Wordpress is relevant here in any way. If the code is poorly written and has N+1 queries problems then it is either poorly written or is trying to half-ass an ORM.
Lastly, I would like a lot of citations to prove to me that ORMs produce more efficient queries. What produces more efficient queries in all my experience is a good understanding of relational databases, better query planners and better database design.
Specialization of requests reduces data transport, but increases testing surface area and drastically reduces caching options - including within the database. Years ago we discovered that our bottleneck in Oracle was being caused by query cache evictions (worse, in 9i only queries currently in the cache could run, so new queries were blocking in the query planner. I'm not sure if this was ever fixed.)
One of the important uses of etags within your infrastructure is to allow you to incorporate data manipulation into your caches. If your functions are deterministic you can store the results of a calculation, instead of the source data for that calculation. Cache hits then don't just save bandwidth, they also save computation, which reduces the sequential parts of the response, fighting Amdahl's Law.
There is a different performance problem lurking with this design though. You have to decide whether to tell EF Core to do a query per relation or everything in one query. The single query is very efficient for many simple cases and ensures consistency, but it can fail spectacularly if you have a very large number of relations. This is a pretty significant footgun, but if you really don't want to be bothered with this you can just always use split queries.
ORMs can't really abstract over all performance considerations. They are convenient, but they can't fully hide what the database is doing under the hood.
There is no gotcha here.
ORM (or GTM if you like) helps you navigate your Graph of Tuples.
The object-relational impedance mismatch is not just something I came up with to shit on ORM. It is a well understood and widely known phenomenon which ORM users often like to insist on pretending doesn't exist. To claim that sets of tuples are the same as object graphs is like claiming that you have solved the halting problem.
Feel free to claim that the impedance mismatch is small enough that a sufficiently advanced ORM might successfully abstract it away, I don't think this claim is defensible either but on the other hand claiming that the OR impedance mismatch doesn't exist makes me think you have no idea what problem an ORM actually solves. It certainly makes me wonder how you would justify the size and complexity of every ORM out there.
That's a set of tuples that defines a graph
SQL is already a perfectly adequate language for describing how to query relational data, by putting a effectively a graph query language in front of it and adding a translation layer you immediately need an immense amount of special casing, the common cases might be handled by a sufficiently sophisticated ORM but once you veer even slightly outside the realm of a basic hello world application which accesses a database you are immediately hit with a wave of all the complexity which results from the abstraction leaking. Every time I've personally dealt with an ORM or seen code which deals with an ORM, the amount of unnecessary hassle which is required just to get the ORM to do what you want it to do certainly outweighs any imagined benefit of using it. If you think SQL is hard to write then you're putting the blinders on to claim that ORMs are easier to deal with.
If you have never hit upon these sorts of issues then you must have never actually needed to do anything non-trivial and performant with an ORM.
But with EF at least those problems aren't really a big deal. You are right that SQL is perfectly adequate - great actually. I generally think in terms of SQL before loading data with a Linq query.
Often I'll do a query that projects to a model/dto. Or if I do need to load the entity and it's relations, I'll craft it just like I would in SQL. You can load the desired related entities first and then when you load the main entity they get auto populated since they are in the context. It's easy to build exactly what you need this way.
So why use an ORM? Well for one, on commands if I load an entity I get change detection for free. I get bulk inserts/updates/deletes for free (Using ef extensions). I get strongly typed queries for free. I'm able to find all references to a particular table or column across my entire code base with a single click. I can build up queries dynamically via linq. There's a lot of huge benefits that save a lot of time.
After that I joined a startup that used rails and I had quite the education in ORMs and N+1 queries. The ORM felt quite restricting and I felt a lot less confident in my code.
As you noted, this is one of the reasons why client-side JavaScript often falls far short of the envisioned benefits. In both cases, however, I would suggest that while the trend is real and should inform your architecture the most important thing is to routinely use your monitoring. Different apps have different performance challenges but they all need to be observable and I’ve seen so many times where people wasted tons of time and resources shooting in the dark because they didn’t have granular monitoring or thought it was too hard.
One of the best example I had was a while back when I inherited a Django codebase which was too slow. One of the developers had spent a couple days rewriting everything in Jinja2 because “Django is slow”, introducing the bugs you’d expect and leading to lots of new custom code to maintain (such as 3 versions of the human size formatting function). Performance didn’t budge. Six months later, I saw the MySQL query counter spiraling up on every page view, installed Django Debug Toolbar, looked at the queries, and spent an afternoon reverting all of the templates and fixing the N+1 queries which had always been the problem. That was tens of thousands of lines of code churn all wasted for what was eventually maybe 50 lines changed against the original codebase.
One thing I introduced which worked well was a test hook which failed based on the query counts for a view. That caught most N+1 issues and is conveniently easy to implement with almost any model.
This is what I do for the most complex queries, where translating them to the ORM would be a time consuming pain. What I do normally is think in SQL and write in the ORM language. After all the API of the database is SQL, not the ORM, and I already know SQL.
In this way it's obvious that you have to write includes(:comments), because it's a join, and it's obvious that the original
object.votes.count
would generate one query each time it is called. I write the separate queries and compose the results instead. I'm not using BatchLoader or other gems. Using directly the ORM is enough.If this query turns out to be important for the performances of the application, I'll think about a way to write it with a single query in SQL to make the database do all the work. Then translate it in ActiveRecord or leave it in SQL if it takes too long. Sometimes it's not obvious how to do it, a problem common to all ORMs of all languages.
Edit: somebody called saila gave an example of such a query in the comments one hour before I wrote my reply.
The with query starts with the ID of the object to which the computed property is attached to. So in this case you would do the same thing as the author and write
WITH comment_vote_count as select comment_id, COUNT(vote_id) from comment left join vote;
The ORM would just treat this like a normal table and do a join based on the comment ID except the ORM provides a convenient API to do this, which it currently does not...
I don't know if the approach is possible with every ORM or if it's just leveraging some Ruby perks, but I can't think of a good reason why you wouldn't use the equivalent everywhere.
But it has been interesting to see the tide turn back towards server-side rendering, relying on partial DOM replacement for client-side updates. For web apps that don't have massive numbers of UI states (like a document editor), it seems like people are rethinking the wisdom thick client-side JavaScript applications, which seem to be one of the main motivators for REST API layers, and the need to efficiently fulfill N+1 queries.
Although, I do remember dealing with the N+1 problem when doing Django server-side apps more than a decade ago, before the dominance of client-side apps. I guess it was more the rise of MVC architecture and the active record pattern (https://en.wikipedia.org/wiki/Active_record_pattern) that brought the N+1 problem, more so than client-side apps.