5 ms·

Don't underestimate the cost of ORMs. The serialisation/parsing to models can make a significant performance impact.

by thdespou 4y ago

aardvark179 4y ago
Don’t rely on estimates and guesses, profile it and check.
mekster 4y ago
If you care for performance, start graduating ORM and learn to write performant SQL yourself.
- higeorge13 4y ago
  In 2 past jobs as a non ruby person in ruby shops i was trying to convince them to write raw sql queries instead of relying on activerecord, but with no luck. I won’t argue about performance but most of the times didn’t even know what and how many queries were being generated..
  - cutler 4y ago
    But ... Rails console.
  - weatherlite 4y ago
    There has to be a really good reason for that. It's just like joining a Spring shop and telling everyone to not use JDBC (or whatever the name of the ORM in Java land)...that's not gonna fly well usually. For a good reason imo.
    - higeorge13 4y ago
      The good reason was that queries were not optimal and when we came up with optimal ones which were not following the access pattern or syntax that activerecord introduces, then the excuse was “…but activerecord…”. If i was a dev i would appreciate some data guy’s opinion in queries, indices, etc while my db crashes every now and then, but it’s either a power play/politics thing to ignore the new guy or the ‘i know activerecord/hibernate/kafka/whatever’ thing and i won’t bother sith anything else that will solve the problem.
      - weatherlite 4y ago
        It's a tradeoff. Everything is a tradeoff...I think ORMs like ActiveRecord do make the code easier to reason about in most cases and are worth the performance hit. All abstractions cost us performance..we could be all writing on bare metal C/C++ but not many web shops want to do that.
    - kaba0 4y ago
      There isn’t really a good reason to avoid ORMs in the general case. You can log the actual queries just fine. In my opinion bad performance comes from inadequate knowledge of a sharp edged tool
- gabrielgio 4y ago
  The thing I learned from working with EntityFramework (aspnet) and Django ORM is that people don't really care to learn how those ORM work, they (the devs) ended up generating utterly unoptimized queries, from adding unnecessary inner joins to loading a ton of nested tables. I'd say for both ORM I mention you can pretty much have total control to tailor your sql to your needs (and still have the benefit of easier refactor). Ofc in the end a raw sql string will always give an edge (see Dapper).
  - pjmlp 4y ago
    Dapper and myBatis FTW!
- hakube 4y ago
  Only to end up writing your own ORM in the end. It's feels like a waste of time writing raw SQL. Plus it's difficult to maintain
  - mekster 4y ago
    When your next project runs in a different framework or language, you'd be using a different ORM. It feels like a waste of time learning ORM. Plus it's difficult to optimize.
- heurisko 4y ago
  I've never used an ORM (Hibernate, Doctrine) when you can't also write the queries yourself and then leave the object mapping to the ORM. It's not an either/or. Often I'll use the ORM for saving, and SQL for retrieving.
basilgohar 4y ago
With all due respect, I feel like this is a vacuous statement. While there is no doubt overhead, I think the network latency of DB connections far outweighs a well-written ORM. What may be happening is an overreliance on ORMs in the sense of using them as a hammer and all functionality is a nail – generating thousands of instances of an ORM vs. one collection containing multiple records O(1) vs O(~n). This is no longer ORMs, but just bad practice in general. In my experience with Laravel, the Eloquent ORM and the Fluent query builder both generate semi-optimal SQL for the majority of cases I've experienced them for, and they're easy to debug and see what's happening. Profiling tools like Debugbar let you see how many instantiations, objects, queries, and Models your page has, none of which you'd have if you skipped the ORM. These are critical to writing sensible code and also having a deeper understanding of how your application works when you utilize abstractions. But they also save you enormous amounts of time. I doubt writing it all by hand would be better or faster except by an expert-level programmer with deep DB knowledge. I don't begrudge the value of knowing what's happening behind the scenes – in fact, I'm saying these tools can help open up understanding what's happening. I learned more about subqueries by seeing the SQL generated by the query builder, as well as how to write them efficiently for MySQL, than by reading about them for years.

Don't underestimate the cost of ORMs. The serialisation/parsing to models can make a significant performance impact.

aardvark179 4y ago

Don’t rely on estimates and guesses, profile it and check.

mekster 4y ago

If you care for performance, start graduating ORM and learn to write performant SQL yourself.

higeorge13 4y ago
In 2 past jobs as a non ruby person in ruby shops i was trying to convince them to write raw sql queries instead of relying on activerecord, but with no luck. I won’t argue about performance but most of the times didn’t even know what and how many queries were being generated..
- cutler 4y ago
  But ... Rails console.
- weatherlite 4y ago
  There has to be a really good reason for that. It's just like joining a Spring shop and telling everyone to not use JDBC (or whatever the name of the ORM in Java land)...that's not gonna fly well usually. For a good reason imo.
  - higeorge13 4y ago
    The good reason was that queries were not optimal and when we came up with optimal ones which were not following the access pattern or syntax that activerecord introduces, then the excuse was “…but activerecord…”. If i was a dev i would appreciate some data guy’s opinion in queries, indices, etc while my db crashes every now and then, but it’s either a power play/politics thing to ignore the new guy or the ‘i know activerecord/hibernate/kafka/whatever’ thing and i won’t bother sith anything else that will solve the problem.
    - weatherlite 4y ago
      It's a tradeoff. Everything is a tradeoff...I think ORMs like ActiveRecord do make the code easier to reason about in most cases and are worth the performance hit. All abstractions cost us performance..we could be all writing on bare metal C/C++ but not many web shops want to do that.
  - kaba0 4y ago
    There isn’t really a good reason to avoid ORMs in the general case. You can log the actual queries just fine. In my opinion bad performance comes from inadequate knowledge of a sharp edged tool
gabrielgio 4y ago
The thing I learned from working with EntityFramework (aspnet) and Django ORM is that people don't really care to learn how those ORM work, they (the devs) ended up generating utterly unoptimized queries, from adding unnecessary inner joins to loading a ton of nested tables. I'd say for both ORM I mention you can pretty much have total control to tailor your sql to your needs (and still have the benefit of easier refactor). Ofc in the end a raw sql string will always give an edge (see Dapper).
- pjmlp 4y ago
  Dapper and myBatis FTW!
hakube 4y ago
Only to end up writing your own ORM in the end. It's feels like a waste of time writing raw SQL. Plus it's difficult to maintain
- mekster 4y ago
  When your next project runs in a different framework or language, you'd be using a different ORM. It feels like a waste of time learning ORM. Plus it's difficult to optimize.
heurisko 4y ago
I've never used an ORM (Hibernate, Doctrine) when you can't also write the queries yourself and then leave the object mapping to the ORM. It's not an either/or. Often I'll use the ORM for saving, and SQL for retrieving.

basilgohar 4y ago

With all due respect, I feel like this is a vacuous statement. While there is no doubt overhead, I think the network latency of DB connections far outweighs a well-written ORM. What may be happening is an overreliance on ORMs in the sense of using them as a hammer and all functionality is a nail – generating thousands of instances of an ORM vs. one collection containing multiple records O(1) vs O(~n). This is no longer ORMs, but just bad practice in general. In my experience with Laravel, the Eloquent ORM and the Fluent query builder both generate semi-optimal SQL for the majority of cases I've experienced them for, and they're easy to debug and see what's happening. Profiling tools like Debugbar let you see how many instantiations, objects, queries, and Models your page has, none of which you'd have if you skipped the ORM. These are critical to writing sensible code and also having a deeper understanding of how your application works when you utilize abstractions. But they also save you enormous amounts of time. I doubt writing it all by hand would be better or faster except by an expert-level programmer with deep DB knowledge. I don't begrudge the value of knowing what's happening behind the scenes – in fact, I'm saying these tools can help open up understanding what's happening. I learned more about subqueries by seeing the SQL generated by the query builder, as well as how to write them efficiently for MySQL, than by reading about them for years.