Hacker News Reader

Comment by manigandham | Hacker News Reader

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise.

The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong data structure for a certain situation.

Lazy-loading a large child object in a tight loop over 100s of entries will always be slow because it's an improper approach, and nothing other than knowledge and competency will resolve that. It's rather amazing ORMs can be so controversial when the real issue is the developer, which speaks to the far bigger problem of expertise and quality in this industry.

View on HN

It is the developer, but ORMs are so controversial in part because they often obscure that you're doing something crazily ineffective in ways that makes developers that don't understand the abstraction fail to see that they're doing something obviously wrong.

It's more stark that you're doing something crazy if you do a SELECT, instantiate objects from each returned row, then apply a filtering rule to that object, than if you're "just" operating on something that looks just like it's all local. Both to you, and to people reviewing your code.

I prefer working with an ORM, but it does take discipline to use an ORM properly; to make sure you know and use the facilities for building queries that return only the rows you want, and instantiate objects with only the columns you need.

I tend to agree with you that it will only be resolved by knowledge and competence, and throwing out ORMs won't solve that. But I also understand the frustration at how many ORM users basically seem to use it as a means to let them pretend there's no database server there, rather than as a tool to generate queries more easily.

Perhaps the anti-pattern to rule them all is that as things become easier to do, it gives more people the opportunity to do them badly. We have so many incredible tools, and simple but powerful high level languages that allow us to write apps without bothering with complicated stuff like assembler. However, these tools don’t mean that we get to ignore how CPUs work, and ORMs don’t mean that we get to ignore how data structures work, or how we’re supposed to operate on them.

I think you’re 100% right. ORMs are just tools, they can be used well or poorly. That’s up to the developer. As a former DBA, I also think they’re great tools. Aside from making the business logic easier to write, they also bring the business logic and the data closer together.

> as things become easier to do, it gives more people the opportunity to do them badly.

ala, this is why we have electron, php, and javascript. None of those things behave badly, but it's very easy to write something useable in those, but resource intensive, or insecure, or poorly maintainable.

>[...] ORMs are so controversial in part because they often obscure that you're doing something crazily ineffective ...

Then just turn on detailed SQL logging while you are coding and keep an eye on the queries generated by your ORM calls.

If you do this methodically for all your DAOs, then you should spot performance problems early when they can still be easily fixed.

With all this continuous effort required, wouldn't it be simply easier to ditch the whole 'use ORM because it makes life easier' nonsense and use just some SQL-mapping tool like iBatis? Where you really, truly have 100% immediate control over queries, no dancing around with logging and wasting time figuring out why the lib decided to fetch this and not that.

I honestly don't get the fear some people have from SQL. If you can't do SQL good enough for 98% of use cases out there, you are not senior dev, not even experienced one. It's just one of those basic dev requirements that won't go away in next 50 years anyway.

True, but on the other hand if the developer gets told they are loading nearly 20,000 objects they should be smart enough to realize something is going to need to be optimized here, and although I don't know how to do it I better find out.

That depends upon the context. I've seen developers argue over optimizing ETL processes that weren't in the slightest bit time sensitive, took 25 minutes and were going to be run once.

More often than not I've seen developers fret over performance when it's not actually bothering users. This fetishization of performance seems to be a cultural thing in tech.

Conversely, data integrity, normalization and transactionality are usually given far too little weight.

> This fetishization of performance seems to be a cultural thing in tech.

Tell me where you've seen that, because I'd love to move there. From my experience, there is a common fetishization of non-performance. The "premature optimization" adage taken to its extreme - "we can buy more hardware", "developer time > machine time", "who cares about wasting electricity of millions of our users", etc.

Pretty sure that is what tailing the log during development is all about. I don't see what is so obscure about it, in the Rails based examples in the post, one can just see the logs flying by with the exact queries being executed.

It is, but a developer that's using ORMs because they're scared of SQL tends to be the same type of developer that will decide not to look at those.

I'm not dismissing ORMs - I use Sequel (the Ruby ORM) for almost all my database access. But I've also seen enough people use ORMs as an excuse to pretend they don't need to understand SQL or understand the database to understand why some people look at ORMs with suspicion.

A lot of code that is obviously bad when the database queries are plain for everyone to see are not so obviously bad when it's less clear if that method call translates to a database query or just extracts data locally.

Note that this is a general issue with this type of abstraction: It is a common complaint against transparent RPC wrappers as well that if they're too good at hiding that an object is remote it's easy for someone to carelessly cause massive amounts of unnecessary roundtrips.

I don't know, I find that a lot of the people that write the most inefficient ORM code would also not spot the inefficiencies in SQL queries either.

On the flipside, I've seen a lot of people that can write efficient SQL queries, but then turn around and ruin their performance with a bespoke code model that inefficiently calls those SQL queries and poorly leaks the memory and database connections while doing so.

A good ORM makes it rather easy to fix an inefficient query of a fellow developer or previous self (almost all of the examples in this article are effectively one-line changes; many of the biggest performance gains I've made in ORM usage have been removing code and/or making it more readable), but fixing the mistakes of a bespoke model can be a huge challenge with a lot of surprises.

Not this again. ORMs are some of the most impenetrable code you'll ever see. Understanding the performance characteristics of a CRUD application using SQL is vastly simpler than trying to puzzle out the arcane ways in which ORMs decide to throw up garbage once you add one more thing to the mix.

It's just code, compiled into a library that you can use, and queries are either logged by the library or in your database. What exactly is so impenetrable? That's just strange. Also CRUD is the ideal scenario for ORMs which handle all the mapping, security, parameterization, and even conversions between data types and models seamlessly while letting you just work with your objects.

It seems you either didn't use ORMs correctly or used a very poor one.

>It's just code, compiled into a library that you can use.

That's also true for code that one shouldn't use.

Dont get me started over the abuse of active record.

Not that again. ORMs achieve one main thing: being able to map your app’s objects to a relational database and back.

But there are tons of other benefits:

1) Avoid all injection attacks by default by binding variables rather than interpolating their vakues

2) Write SQL code for you to automatically, so you always have balanced parentheses and no typos or errors mixing statements

3) Autogenerate classes and methods from the SQL model automatically so there is a place where you can add custom methods on objects

4) Model fields and relationships in a way the app can understand, so you can make meaningful error checks and manipulations in the app instead of relying on the database engine to give you a nice error message or manually copying the data type logic.

5) Play nice with version control, making sure to encapsulate the code in ONE PLACE instead of a million places when the schema or model changes.

6) Pissing people off on HN so we can better discuss and explain principles of architecting good softeare while talking anout the benefits of ORM

7) Let you write an adapter to move to eg a graph database which is far faster.

8) In fact, just making you avoid doing joins in the database by default is already a feature as you can make your app far more scalable w sharding and possibly think about making it byzantine fault tolerant and distributed!

See for example this:

https://qbix.com/platform/guide/database

https://qbix.com/platform/guide/models

I was with you some of the way but "making you avoid doing joins in the database" made me drop my monocle. You want joins in the database, they are designed for joins. Moving joins to the client will kill performance and scalability.

And any sane ORM will perform the joins in the database by default.

No, that's not true if you're building a huge site. Google has been avoiding joins as early as 2005.

Joins were good for the smaller websites, but they don't scale. By avoiding joins, you have shared-nothing models that can be partitioned horizontally aka sharding.

Now true, the latest and greatest databases such as CockroachDB go out of their way to try to do joins for you across partitions, even in an ACID manner, but then you have to use those. Better to avoid joins in the DB and do it in the app. You can then use a graph database instead of a relational database, going from O(log N) lookups to O(1) lookups for related data.

Oh and finally, the newest (and pretty cool) craze of BFT, Byzantine Fault Tolerance. You can't achieve that if you're doing joins across different publishers, because they're not supposed to be able to access each other's stuff "just like that".

Our ORM supports joins, even with multiple indexes, it even lets you define relationships and figures out the joins FOR YOU, but it is discouraged if you're building scalable sites.

  By the way thank you for proving point #6 hehe

> Joins were good for the smaller websites, but they don't scale

Most sites do not have to scale beyond this limitation (or can use database followers to throw a bit of money at the problem). Providing Google as an example is a bit exaggerated as almost nothing in the world has the scaling needs that google has.

So you have to use sharding because your databases have limited capacity. But you can just join in the app, because the app have unlimited memory?

The app doesn’t hold the entire database at a time. The app simply does the following:

1) Get the root record(s) from id(s)

2) See what related records it needs, combine them into a list of ids, partition list by shard

3) Ask each shard for the corresponding records

4) Repeat from 2 if necessary

5) Return this whole tree / graph to the user

Graph databases can do this in O(1) instead of O(log N) lookups.

Relational joins are just one way to achieve this, which can be made atomic in the ACID sense.

However, as you scale up your website, eg with 100,000,000 users, it would be silly to do massive joins. Google even says this in their docs now, for BigQuery.

Instead, design your systems from the beginnig to be as parallel as possible, if you think they will scale.

Look at the problems with Ethereum for example. Or Twitter fail whales of the past.

A graph database basically caches related entities with the root entity, so lookups are fast when the query follows the paths of the graph. The price is that any other query is extremely costly. But if it works for your use case, more power to you. But not really relevant in a discussion about ORMs - Object Relational Mappers.

The relational model was designed to address the limitations of the network/graph database, especially to allow arbitrary (ad-hoc) querying and to decouple the physical storage from the logical model. But if you don't need all that, a graph database may be fine.

Most applications don’t need that. But if they do, just have indexes!

Acolyer again:

https://blog.acolyer.org/2017/07/07/do-we-need-specialized-g...

For the vast majority of use cases regular SQL databases blow graph databases out of the water. Unless you go for graph-specific algorithms like Shortest Path, and even then...

We are not all google, im extremely happy with my joins and perfomance of Mysql.

>The issues with performance are almost always with the way the tool is used

That's overly generic.

The truth is that some tools encourage bad performance habits and a lax attitude about it whereas others don't. ORMs do.

Encouragement and attitude doesn't force you to do anything. You still have to choose to use it, either well or poorly.

This is no different than any other tool that makes things easy but with obvious limits. Proper decision making is still up to you. There really isn't much controversial here if you get past the whole "ORM" hype/hate cycle.

>Encouragement and attitude doesn't force you to do anything. You still have to choose to use it, either well or poorly.

I don't believe in choices, people are flimsy. I believe in creating an environment that encourages good behavior.

> I don't believe in choices

...ok, people still make choices though, you're not controlling their minds. Perhaps educate your workforce so they make the right decisions by themselves, it's more effective and takes less effort than trying to coerce them through generalizations.

>...ok, people still make choices though, you're not controlling their minds.

No, but as a PM you can dictate they don't use an ORM.

Try reading the 2nd part of my comment.

I think the benefit of using an ORM outweighs the cost of making sure you don't have bad programmers on staff. We should be utilizing code reviews and proper source management anyway, right? This should be as simple as a senior/lead developer seeing someone iterating over 8MM rows and saying "don't do that."

Just a UI issue.

Compilers will warn you if you do stupid common mistakes. Not all mistakes, but many of the stupid common ones. If you make stupid common mistakes with an ORM, why doesn't the ORM warn you?

They require rather sophisticated analysis to detect. A compiler will detect trivial errors, but will not flag if you are using an O(n) algorithm when an O(1) could be used. Not yet, anyway.

I read the paper. A third, maybe half of the stuff the ORMs didn't detect looks simple to detect.

(Greybeards may remember how perl's -W switch switch suddenly detected a frightful amount of performance problems using mostly simple tests. Almost 20 years ago now.)

Maybe you are on to something. All the major ORM's are open source (AFAIK) so if you made a write-up of the common issues and how you suggest they could be detected by the ORM, I think it would be very well received.

I'd think you could do warnings on this simply by processing a log of the queries, looking for query patterns that match common poor code patterns.

E.g.

    SELECT id FROM table [some conditions]

followed by a number of

   SELECT * FROM table WHERE id = ...

is one example of a anti-pattern that suggests that someone is doing an overly simplistic query followed by a loop. Similar with signs of triggering loading of related objects instead of a JOIN.

Doing it on the emitted SQL would also make it quite easy to make it reasonably ORM agnostic - it doesn't need to be perfect, after all, so doing relatively crude pattern matching ought to be able to find at least the more basic problems.

The authors of the paper, if I’m remembering from reading it earlier today, built a static analyzer to help find these types of problems! Some of the work has already been done!

Reply on news.ycombinator.com