4 ms·

If performance is mission critical - ORMs seem like extra hoops to jump through. Am I missing something?

phailhaus 3y ago
ORM's allow you to make changes without having to refactor every single SQL statement you've written manually. They can also be typed, unlike raw SQL strings, which helps a lot during development. It's another abstraction, just like the one between your language of choice and assembly code.
- deleted 3y ago
  [deleted]
brandall10 3y ago
It's a higher level abstraction that that boosts dev speed and readability and is often good enough by default, esp. for CRUD work. More advanced ones have the ability to optimize things fairly well within the constraints of their API. And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.
ryanjshaw 3y ago
Modern ORMs are pretty efficient [1]. Some developers use the ORM for the "write model" and native SQL for reads/reports, if you are read-heavy. [1] https://michaelscodingspot.com/npgsql-dapper-efcore-performance/ https://michaelscodingspot.com/npgsql-dapper-efcore-performa...
BurningFrog 3y ago
It can be, but in real applications only a small percentage of queries are performance problems, and you're probably better off optimizing those than writing and maintaining hand written queries.
ivanhoe 3y ago
It's Pareto principle at play, in RL you usually need to highly optimize just a very small portion of the queries - let's call it 20% for the sake of tradition, though it's realistically <5% IME - to get almost all of the speed-ups worth the trouble. ORMs then help you to write the other 80% quickly and in a maintainable fashion.
kayodelycaon 3y ago
Manually writing queries has a lot of overhead cost. Your entire application now has the UI directly tied to what the query returns. You can make intermediate classes to separate the two but eventually you'll end up making a worse ORM you need to test and maintain. What an ORM gives you is a tunable level of abstraction. I know Ruby on Rails best so I'll use it as an example. ActiveRecord has a lot of tuning you can do, like partial selects and snippets of sql where you need it. If that isn't enough, there is find_by_sql as an escape hatch. If that is still not fast enough, you can call ActiveRecord::Base.connection and skip the ORM entirely. This will keep complexity limited to places where it needs to be complex. In my experience, every level of optimization you do comes with decreasing maintainability.
- randomdata 3y ago
  > Your entire application now has the UI directly tied to what the query returns. Not really. Without ORM, your entire application is now tied to sets of tuples (i.e. relations) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. You could still have a transformation from one relation to another. Not since the PHP days of embedding MySQL calls right in the middle of HTML have I seen anyone carry relations from end-to-end, though. The idea that anyone is not doing ORM these days seems like a straw man.
  - lelanthran 3y ago
    > Not really. Without ORM, your entire application is now tied to relations (i.e. sets of tuples) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. And, in some edge-cases, the relation is more useful than the object graph.
  - jimbokun 3y ago
    Depends. Clojure uses objects rarely. But still has really powerful abstractions for writing applications quickly.
evantbyrne 3y ago
Yes. Every mature ORM that I'm aware of includes tools to trivialize fetching related records in an efficient manner. All people typically have to do is call the right method. In Django, for example, these methods are select_related and prefetch_related. Also, they don't have to be slow. When benchmarking my hobby golang ORM, I found the performance cost vs hand writing optimal SQL to be in the microseconds per returned row. Small price to pay for everything it gives you: schema migrations, query builder, object mapping, validation, etc. And if you happen to have a particularly hot endpoint that needs to return tens of thousands of rows at once during a HTTP lifetime, you can always dip back down to the driver level. It pains me that every time the topic of ORMs come up on HN that the comments are full of people warning not to use such a powerful category of tools. Maybe the tools they are using aren't very mature, maybe the underlying technologies are bit dated, maybe the documentation doesn't emphasize performance enough. I'm not really sure what's going on. Hopefully we can improve things so fewer people have issues and instead see performance improvements from using ORMs.
- lrem 3y ago
  I haven’t seriously used an ORM for the simple reason that I find SQL simple enough to not need to learn an extra DSL to hide it. Furthermore, when I’m done figuring out the query I need in a Jupyter notebook (technically Google colab, should be same), I can just paste it to the C++/Go server or the dashboard config. Raw performance didn’t ever matter that much to me. With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
  - evantbyrne 3y ago
    Have you ever used a rapid development framework like Django or Laravel? A good ORM is basically the core of any decent backend framework that interfaces with a RDBMS. You get to define a schema once which can then be used as a foundation for migrations, validation, queries, serialization, etc. Many ORMs may only help solve one or two of those things well, and in personal experience it is rare to see even okayish JavaScript ORMs, which may contribute to why people hate the category of tools.

ORM's allow you to make changes without having to refactor every single SQL statement you've written manually. They can also be typed, unlike raw SQL strings, which helps a lot during development. It's another abstraction, just like the one between your language of choice and assembly code.

deleted 3y ago
[deleted]

brandall10 3y ago

It's a higher level abstraction that that boosts dev speed and readability and is often good enough by default, esp. for CRUD work. More advanced ones have the ability to optimize things fairly well within the constraints of their API. And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.

ryanjshaw 3y ago

Modern ORMs are pretty efficient [1]. Some developers use the ORM for the "write model" and native SQL for reads/reports, if you are read-heavy. [1] https://michaelscodingspot.com/npgsql-dapper-efcore-performance/ https://michaelscodingspot.com/npgsql-dapper-efcore-performa...

BurningFrog 3y ago

It can be, but in real applications only a small percentage of queries are performance problems, and you're probably better off optimizing those than writing and maintaining hand written queries.

ivanhoe 3y ago

It's Pareto principle at play, in RL you usually need to highly optimize just a very small portion of the queries - let's call it 20% for the sake of tradition, though it's realistically <5% IME - to get almost all of the speed-ups worth the trouble. ORMs then help you to write the other 80% quickly and in a maintainable fashion.

kayodelycaon 3y ago

Manually writing queries has a lot of overhead cost. Your entire application now has the UI directly tied to what the query returns. You can make intermediate classes to separate the two but eventually you'll end up making a worse ORM you need to test and maintain. What an ORM gives you is a tunable level of abstraction. I know Ruby on Rails best so I'll use it as an example. ActiveRecord has a lot of tuning you can do, like partial selects and snippets of sql where you need it. If that isn't enough, there is find_by_sql as an escape hatch. If that is still not fast enough, you can call ActiveRecord::Base.connection and skip the ORM entirely. This will keep complexity limited to places where it needs to be complex. In my experience, every level of optimization you do comes with decreasing maintainability.

randomdata 3y ago
> Your entire application now has the UI directly tied to what the query returns. Not really. Without ORM, your entire application is now tied to sets of tuples (i.e. relations) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. You could still have a transformation from one relation to another. Not since the PHP days of embedding MySQL calls right in the middle of HTML have I seen anyone carry relations from end-to-end, though. The idea that anyone is not doing ORM these days seems like a straw man.
- lelanthran 3y ago
  > Not really. Without ORM, your entire application is now tied to relations (i.e. sets of tuples) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. And, in some edge-cases, the relation is more useful than the object graph.
- jimbokun 3y ago
  Depends. Clojure uses objects rarely. But still has really powerful abstractions for writing applications quickly.

evantbyrne 3y ago

Yes. Every mature ORM that I'm aware of includes tools to trivialize fetching related records in an efficient manner. All people typically have to do is call the right method. In Django, for example, these methods are select_related and prefetch_related. Also, they don't have to be slow. When benchmarking my hobby golang ORM, I found the performance cost vs hand writing optimal SQL to be in the microseconds per returned row. Small price to pay for everything it gives you: schema migrations, query builder, object mapping, validation, etc. And if you happen to have a particularly hot endpoint that needs to return tens of thousands of rows at once during a HTTP lifetime, you can always dip back down to the driver level. It pains me that every time the topic of ORMs come up on HN that the comments are full of people warning not to use such a powerful category of tools. Maybe the tools they are using aren't very mature, maybe the underlying technologies are bit dated, maybe the documentation doesn't emphasize performance enough. I'm not really sure what's going on. Hopefully we can improve things so fewer people have issues and instead see performance improvements from using ORMs.

lrem 3y ago
I haven’t seriously used an ORM for the simple reason that I find SQL simple enough to not need to learn an extra DSL to hide it. Furthermore, when I’m done figuring out the query I need in a Jupyter notebook (technically Google colab, should be same), I can just paste it to the C++/Go server or the dashboard config. Raw performance didn’t ever matter that much to me. With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
- evantbyrne 3y ago
  Have you ever used a rapid development framework like Django or Laravel? A good ORM is basically the core of any decent backend framework that interfaces with a RDBMS. You get to define a schema once which can then be used as a foundation for migrations, validation, queries, serialization, etc. Many ORMs may only help solve one or two of those things well, and in personal experience it is rare to see even okayish JavaScript ORMs, which may contribute to why people hate the category of tools.