Django ORM mainly hides the SQL from the developer (which is kind of the idea of any ORM). But if a developer does not understand the underlying SQL concepts, they will soon write performance wise horrific code. But, if you remove the ORM from that equation, I don't see how that same developer doesn't make the same mistakes. So in the end, I don't think that Django ORM (in its core) can help much in this area.
There are of course tools that can help with that (django-debugger and others).
This is a hot-take from Aaron Patterson, Rails and Ruby core team dev, in his keynote this year where he addressed a very similar idea on a perhaps related query generation topic...
To give you the tl;dr (he goes through profiling and a great deal of data to help show what a core dev needs to do in order to help us solve this one specific case, and...)
Aaron comes to the conclusion that there is a "performance pro-tip" which many Rails devs have learned, similar to what y'all are discussing in this thread for Django, and in this one specific case he outlines in some detail, Aaron considers that there is a bug where the programmer needs to know this "pro-tip."
As there was really no reasonable way for the Rails engine to interpret this instruction from the programmer as to mean "do it the slow way" when there's a better way to do the same thing with no drawbacks, and it would have been possible for the engine to safely do the smarter thing (it was IIRC to precompile some better-shaped SQL, bringing back the same result set in fewer queries, (making up the most runtime performance by spending less time in the SQL compiler overall, in the worst performing pathological case, all this is shown with data)...) and that having this behavior here is actually much better for a novice programmer's behalf, in order to make that better performance happen without requiring a developer to manually build such hints into the application code, at abstraction layers where they really shouldn't have to be thinking about those things anyway.
While a good point, I would say the problem is caused by ORMs existing. Somehow somewhere we decided that a person who thinks SQL is too difficult should be using a database.
An ORM saves experienced SQL users from having to write boilerplate SQL and hack on their own garbage ORM, which is what any large project ends up doing, attempting to compose queries and filters in vain.
I have never ever heard of ORMs as an argument to avoid learning SQL, and AFAIK no author of well-known ORMs holds that opinion.
Yeah, my SQL is admittedly rusty due to not hand-writing many queries these days, but my motivation for using an ORM isn't "I can't write SQL", it's that I don't want to be engaging in string building when there are better abstractions available for supporting this kind of operational composition.
The problem with ORMs, as I see it, is that their abstraction is typically too high level and rarely offers you an intermediate layer to let you work around the leaks.
So you often have:
ORM -> SQL
Which is implemented as:
ORM -> private query-builder API -> SQL
When what I want is:
ORM -> public query-builder API -> public SQL-like abstraction -> SQL
Anecdata: in every single company I worked, tiny startups and giant corporations alike, “not everyone knows SQL well” was exactly the argument used to justify the ORM. Every single time, the people who do not know SQL, unsurprisingly, proceeded to write pathological N+1 queries.
I don’t mind much - I get to look like a hero to my manager making their queries orders of magnitude faster. But if someone can’t be bothered to write SQL, they for sure will not bother looking up ways to hint the ORM.
Literally every pro-ORM dev that I've ever worked with was so incredibly weak with basic SQL fundamentals that I must conclude that they preferred ORMs simply due to a reluctance to learn SQL.
There is a strong argument for conceptual compression, the idea that every component has an optimum abstraction layer to understand it at, and with that, less-optimum layers for understanding. An ORM that does its job well allows you to take the complexity and pack it away for another day. We can unpack it later when we need to understand those details, but most of the time we'd rather be focused on the details of a domain-specific problem that we're trying to solve, with intricacies that are specific to the domain, that ideally need not become compounded against problems introduced separately by (for example) a persistence layer such as a database, as that makes it harder for the domain experts by introducing another layer of complexity that they must grok in order to build or spec an appropriate solution.
I would be very hesitant to turn an ORM into a "smart" SQL generator. Depending on the type/distribution of data, there are sometimes very different paths to optimizing a query. The ORM as a "stupid" SQL generator (straightforward mapper) is a great way to allow for the flexibility to control how a query is generated.
The DB engine might be the place for those sort of improvements.
In this case the DB engine was not the place for those improvements, as the majority of the extra time was being spent in the "Compile" method which merely composes SQL. If you're worried about turning the ORM into a smart sql generator, having second thoughts about that, in Rails and in ActiveRecord, I'd have to say that ship has already sailed. The ORM is very smart and usually generates pretty good SQL for you. It's never a bad idea to review what it generated and double check based on your own DB knowledge, which might always be better informed than a robot's, but...
Early in the profiling part of the talk Aaron shows how he nearly got hoodwinked into thinking that differences in the query itself were what was causing the slowdown, and while in the end two different queries are still generated by the two different versions of ORM code, the bulk of the performance capture is reclaimed without any impact from the query difference, at least a ~30% performance discrepancy comes solely from the object side of the equation, in Ruby.
(Is it a string or integer parameter? That might make a difference in the query performance... is it one bound array parameter, or one parameter binding per array element? That could only make a difference on the Ruby side, as bound SQL params are always mapped into a query as individual values, at least in this example. These factors are all in play.)
It's a long talk but it's really interesting, (I set a time index in the link to get you past the most frivolous and off-topic parts, which you usually find in a tenderlove talk... that part is not for everyone) I think this talk probably has something for everyone, even if you're not a Rails dev.
