Hacker News Reader

Comment by gemstones | Hacker News Reader

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.

View on HN

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.

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.

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

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.

> 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.

thatsthejoke.jpg

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…

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.

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.

> 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.

"raw sql" is table stakes, core competency for every software engineer

ORMs 100% always do it worse

Reply on news.ycombinator.com