[lmm]

This kind of thing is the reasons why I do like ORMs. Even if you can see the SQL, that doesn't mean you can tell what it's doing: you don't know what indices will be used or not used, you don't know why the query planner will do things one way or another, you're not actually "close to the metal" at all. So might as well have the convenience of an ORM; sure, you'll have to do some fiddly profiling as and when you have performance problems, but even if you were using hand-written SQL you'd still have to do that.

[devmunchies]

> Even if you can see the SQL, that doesn't mean you can tell what it's doing

You can. I mentioned EXPLAIN in my comment.

And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used.

Admittedly it’s not as easy as using an ORM, but if you’re a SQL expert then you can make queries much more optimized. You’ll never internalize how a SQL interpreter reads your queries unless you do it.

I’m talking about huge tables that are hit many times a second, where you need to start thinking like a Formula One team, being creative with queries to shave off hundreds of milliseconds.

[lmm]

> And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used.

If you're willing to put that kind of time and effort in, you should have no trouble understanding how your ORM generates queries - IME they tend to be far clearer, better documented, and more introspectable than database query planners.

[mumblemumble]

It's not about whether one can or cannot, it's about whether one does or does not.

Also, there's a feedback loop to consider. You can choose to get to know your DBMS, or you can choose to hold it at arm's length. Both can be reasonable options, but only one is going to foster expertise.

I've worked in enough places to see a clear pattern: Companies that use ORM for anything beyond really simple CRUD tend to have creeping performance problems with their database. (And typically also a healthy contingent of team members pushing to solve those problems by doing something drastic like migrating to NoSQL.) Companies that don't use ORM generally don't have the same problems.

For my part, I find micro-ORMs such as Dapper to be the sweet spot. They get the vast majority of the convenience benefit, without encouraging poor housekeeping practices.

[devmunchies]

It’s not that I’m willing to put in the effort, it’s that the scale I operate at requires it. I would rather become highly proficient at SQL and use it universally with any server language than become an expert at a specific ORM.

But I don’t see how one could become an expert at writing ORM queries without knowing the underlining SQL, which means you’re putting in the time to master two languages.

I may be just be an outlier, and that’s fine. I like geeking out over SQL optimization.

[lmm]

> But I don’t see how one could become an expert at writing ORM queries without knowing the underlining SQL, which means you’re putting in the time to master two languages.

It's like working in a transpiled language: you need to understand the intermediate language a little, but you don't need to master it. Indeed I'd argue that the ORM often corresponds more clearly to what's actually going on at the query planner level than the SQL does: pulling out an entity via an indexed link to another entity is very different from scanning through a table for cases where one value matches another, and they look different in the ORM and in the query planner, but in SQL they're both just "JOIN".