I can relate to this. It’s so easy to write a some code that fires off 10 queries but it’s obscured by the ORM and then it’s in an index listing which isn’t paginated and eventually some user does things weird and it ends up with 1000 items so this one listing runs 10,000 queries.
In my experience, it doesn't take the user doing anything weird - all it takes is to use ORM enough in the project, and suddenly you have hundreds of separate queries being run to fetch something trivial, which could be done in one or two queries of hand-written SQL.
In my experience, those "one or two queries of hand-written SQL" quickly balloon out of control into a litany of crimes, because the assumption they will be better relies on having the kind of developer who can easily write "one or two queries of hand-written SQL" but are somehow incapable of reading the documentation or using an ORM correctly.
Writing SQL is so much easier than understanding ORMs. ORMs tend to be very complex, because the problem they're trying to solve requires that complexity.
Now maybe my perspective is biased too much, since the only ORM I've really used is Hibernate. But in that experience, I learned that Hibernate brings a ton of complexity with no real value. Seems like a high price to pay to avoid learning SQL.
The sweet spot seems to be using a library like JOOQ, which lets you write queries, as well as safely construct queries at runtime.
