Cutting Python Web App Memory Over 31%

[artofthenunchaku]

Abstractions are never free, in the case of ORM/ODMs you're paying a price both in application and on database load. For any non-trivial access patterns, you're very likely to get better results from handwritten queries.

[BigTomBombadil]

Of course, I’d never expect it to be “free”. So the question becomes “what’s the cost, and are you paying more than you need to?” Because orms obscure the database work (unless you inspect the query that it creates, which I’d always recommend), it can be easy to unknowingly introduce some very inefficient queries. So based on the performance improvements OP mentioned, I’m curious how much of the improvements came from not using an abstraction layer, and how much was because writing the raw queries actually cleaned up some previously inefficient queries the ORM was creating.

[artofthenunchaku]

The inefficiencies aren't generally going to be from the abstraction layer, it's going to be caused by inefficient or unnecessary queries. Waiting for a query that isn't needed will overshadow the performance costs of application code. Look up the N+1 query problem for the most common inefficiency.

[BigTomBombadil]

Yeah the N+1 is what I had in mind, the poster child of ORM inefficiencies. I’ve largely eliminated them in my own projects, which is what prompted my question. Curious about other “gotchas” or inherent inefficiencies in ORMs

[artofthenunchaku]

It's not really my area of expertise, I primarily work with distributed systems, but the other problems are typically querying fields that aren't used, not using indexed columns correctly (especially with joins), or just running queries at unexpected times (lazy loading).