4 ms·

> I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls That doesn’t sound like ORM… More like an N+1 problem.

by jw1224 4y ago

> I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls That doesn’t sound like ORM… More like an N+1 problem. Eager-loading makes N+1 more likely with ORMs, but it’s easy to avoid when you know what to look for. ORMs are designed to reduce querying, not increase it a thousand-fold :)

eyelidlessness 4y ago
N+1 is a common problem with many ORMs, and a classic example of the object-relation impedance mismatch. Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory. Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.
- noduerme 4y ago
  Upfront: I really dislike ORMs. They're an unnecessary abstraction and a performance headache as an extra layer of middleware to worry about keeping up to date. I'd never use one in production. But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.
BiteCode_dev 4y ago
Right. In fact any ORM worth its salt will have methods specifically designed to let you address the N+1 problem easily. E.G: django "select_related" Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query. Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with. In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".

by jw1224 4y ago

eyelidlessness 4y ago

N+1 is a common problem with many ORMs, and a classic example of the object-relation impedance mismatch. Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory. Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.

noduerme 4y ago
Upfront: I really dislike ORMs. They're an unnecessary abstraction and a performance headache as an extra layer of middleware to worry about keeping up to date. I'd never use one in production. But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.

BiteCode_dev 4y ago

Right. In fact any ORM worth its salt will have methods specifically designed to let you address the N+1 problem easily. E.G: django "select_related" Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query. Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with. In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".