Frequently the easiest path to perform a complex query with an ORM results in N+1 performance disasters. The inevitable retort is something like "but the ORM has better ways to do that." Of course it does; ORM implementers aren't incompetent. The key word is "easiest," meaning the ORM user doesn't have to spend time learning anything beyond simply chaining method calls. In production such work goes haywire when resolving some related entity causes tens of millions of round trips.

Without an ORM the easiest path is to write a single join, giving the database optimizer a fair shot at correctly optimizing the query, including over time as the database evolves. Again, yes an ORM can encapsulate the equivalent query. Guess what; for every time these ORM capabilities are actually utilized there are probably hundreds of expediently written N+1 queries because the programmer either didn't know better or was indifferent.

To a pragmatic mind this is the inevitable outcome when a tool makes the inefficient solution the easiest thing to do.

I look at this as not playing to core competencies.

SQL's strength is that it's declarative.

By wrapping an ORM around it, suddenly you have a DB engine talking to an ORM engine through a declarative interface. There simply isn't enough information and context passed across the interface to properly optimize.

So in the end, it's like trying to run two optimizing compilers in series, where either knows the specs of the other.

It's the good old "pit of success" story. When doing the right thing is extra work, you will find countless examples of people doing the wrong thing. The only way to always do the right thing is to make that the easiest possible way.

I've only worked with a couple ORMs, but they all allowed me to write my own queries if I wanted to. The ORM generated 90%, but when there was a performance win I just wrote my own.

Yes, this happens with complicated schemas with complicated queries. ORMs account for this. Almost every major ORM allows you to override those "bad" queries when needed with your own manual SQL calls. It's an old problem with an old solution that works in production.

Most ORMs allow you to write SQL, no? So why not just use that when appropriate?

So, I'm the 'database guy' and I come in when the DB is on fire. I find the bad query(ies) and tell the application developer they need to do it another way. (And suggest ways)

Then they tell me, it's too hard / impossible to do something else, and that's after it took them two weeks to figure out how the query is formed.

For these applications, it's best to ensure their DB is isolated from other DB needs and let the tire fire burn as a signal to others, because I can help a lot with performance, but adding indexes can only help so much.

When I've had similar experiences with people running bad queries from strings, it's a lot easier to get traction on fixes. Even when they think my suggestions are crazy (nobody likes it when I suggest a client side join, however, sometimes it's significantly faster)