4 ms·

Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a

Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a complex query are immediately negated by a junior turning what an ORM would write as a single query into three DB calls. All because SQL insists on a flat result set you have to turn into a nested collection yourself, without an ORM doing it for you with eager loading.

nine_k 3y ago
From my experience, ORMs in hands of junior.developers who happen to not yet know SQL are a disaster. However hard the ORMs may try, the code ends up making a ton of small queries instead of one efficient query, and fetching a ton of unused columns. The developers then end up doing joins manually in application code, some distance further from the place of the original queries. ORMs also tend to sneak "live" objects into unexpected places, triggering surprise DB accesses. Not that ORMs are completely useless. We just need to stop pretending that there can be a smooth and performant automatic mapping between relational tables living in a DBMS and Business Objects living in some idealized world without storage limitations, where any connection between them is like following a pointer in RAM. Most ORMs provide tools to write composable, reusable queries and parts thereof. These are the best parts.
- drbawb 3y ago
  Every time I have used an ORM I end up supplementing or replacing it with a "query DSL" like jOOQ, Linq, Arel, Ecto, diesel, etc. I seem to have the opposite problem of OP: I don't often find myself wanting to hydrate some complex object graph, what I really want is some small fraction of what constitutes "an object": "get me the distinct values of this column, sorted by another column", or "get me a list of user IDs and e-mails that are subscribed to this topic", etc. Trivial to do in SQL, and much faster to do that sort of thing _in the database_, where the data is already _memory/cache resident._ ORMs, by design, bring unnecessary data over the wire for the sake of inflating parts of an object graph you don't care about 90% of the time. Most of that data will either be unused, or you are going to transform and then discard anyways. If you go out of your way to actually optimize out unused fields: now you're passing around objects with nulled-out references around your application, which is just a disaster waiting to happen. Having a query DSL that actually maps result sets to your language's type system is the only way I've found to actually write robust, performant, maintainable code. My result sets being "too big cartesian disasters" is just not a problem I have, because I don't think in objects. I ask the database for what I want to get the job done.
  - x-shadowban 3y ago
    Often we just want "totally adhoc result set, but constrained using some common where or join." We keep on with the orm, but it's basically a slow and complicated form of a view at this point
- 0x445442 3y ago
  These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications. What was once a single API call for the application, backed by a SQL query joining over tables has been replaced by multiple micro service calls. It’s now the application’s responsibility to hold these MS call results in memory and then relate them once all the data has been fetched. This works well because the failure rate of http requests is so much lower than a DB query. And after all, the further away the code from the data the better equipped it is to reason about the data.
  - DaiPlusPlus 3y ago
    > These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications This is utter nonsense. There is absolutely nothing about microservices architecture that relates to performing JOINs in SQL.
    - kmeisthax 3y ago
      thatsthejoke.jpg
eastbound 3y ago
I’ve had a first-of-class linuxian excellent developer but junior, tell me that we need Kafka because our SQL requests took 3 seconds. It should be a single INSERT, but through an ORM that multiplies it. The only upside of Kafka is not having the ORM…
- j16sdiz 3y ago
  If you need pre-fetching and 2-phase-commits, a database designed for queues can easily work 3x better than a genetic SQL database. That's being said, Kafka is not one of them.
- gemstones 3y ago
  In this case, you are the senior that needs to bypass the ORM and just use whatever raw parameterized query support exists in your ORM of choice.
  - DaiPlusPlus 3y ago
    > whatever raw parameterized query support exists in your ORM of choice That doesn't help with conditional-predicates - that's another major shortcoming in SQL.
preseinger 3y ago
"raw sql" is table stakes, core competency for every software engineer ORMs 100% always do it worse

nine_k 3y ago

From my experience, ORMs in hands of junior.developers who happen to not yet know SQL are a disaster. However hard the ORMs may try, the code ends up making a ton of small queries instead of one efficient query, and fetching a ton of unused columns. The developers then end up doing joins manually in application code, some distance further from the place of the original queries. ORMs also tend to sneak "live" objects into unexpected places, triggering surprise DB accesses. Not that ORMs are completely useless. We just need to stop pretending that there can be a smooth and performant automatic mapping between relational tables living in a DBMS and Business Objects living in some idealized world without storage limitations, where any connection between them is like following a pointer in RAM. Most ORMs provide tools to write composable, reusable queries and parts thereof. These are the best parts.

drbawb 3y ago
Every time I have used an ORM I end up supplementing or replacing it with a "query DSL" like jOOQ, Linq, Arel, Ecto, diesel, etc. I seem to have the opposite problem of OP: I don't often find myself wanting to hydrate some complex object graph, what I really want is some small fraction of what constitutes "an object": "get me the distinct values of this column, sorted by another column", or "get me a list of user IDs and e-mails that are subscribed to this topic", etc. Trivial to do in SQL, and much faster to do that sort of thing _in the database_, where the data is already _memory/cache resident._ ORMs, by design, bring unnecessary data over the wire for the sake of inflating parts of an object graph you don't care about 90% of the time. Most of that data will either be unused, or you are going to transform and then discard anyways. If you go out of your way to actually optimize out unused fields: now you're passing around objects with nulled-out references around your application, which is just a disaster waiting to happen. Having a query DSL that actually maps result sets to your language's type system is the only way I've found to actually write robust, performant, maintainable code. My result sets being "too big cartesian disasters" is just not a problem I have, because I don't think in objects. I ask the database for what I want to get the job done.
- x-shadowban 3y ago
  Often we just want "totally adhoc result set, but constrained using some common where or join." We keep on with the orm, but it's basically a slow and complicated form of a view at this point
0x445442 3y ago
These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications. What was once a single API call for the application, backed by a SQL query joining over tables has been replaced by multiple micro service calls. It’s now the application’s responsibility to hold these MS call results in memory and then relate them once all the data has been fetched. This works well because the failure rate of http requests is so much lower than a DB query. And after all, the further away the code from the data the better equipped it is to reason about the data.
- DaiPlusPlus 3y ago
  > These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications This is utter nonsense. There is absolutely nothing about microservices architecture that relates to performing JOINs in SQL.
  - kmeisthax 3y ago
    thatsthejoke.jpg

eastbound 3y ago

I’ve had a first-of-class linuxian excellent developer but junior, tell me that we need Kafka because our SQL requests took 3 seconds. It should be a single INSERT, but through an ORM that multiplies it. The only upside of Kafka is not having the ORM…

j16sdiz 3y ago
If you need pre-fetching and 2-phase-commits, a database designed for queues can easily work 3x better than a genetic SQL database. That's being said, Kafka is not one of them.
gemstones 3y ago
In this case, you are the senior that needs to bypass the ORM and just use whatever raw parameterized query support exists in your ORM of choice.
- DaiPlusPlus 3y ago
  > whatever raw parameterized query support exists in your ORM of choice That doesn't help with conditional-predicates - that's another major shortcoming in SQL.

preseinger 3y ago

"raw sql" is table stakes, core competency for every software engineer ORMs 100% always do it worse