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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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.

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.

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.

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

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.

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.

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

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

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

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?

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

E.g.

    SELECT id FROM table [some conditions]

   SELECT * FROM table WHERE id = ...
 

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 problem is not ORMs, the problem is just people not thinking about the resources their query over some data takes, and trying to do things in memory that are better done in the database (as in your example), because databases are hard, and sql is not a particularly friendly language, so they take the easy way out. They'd do things like iterating over all instances just as much if not using a framework/ORM, because they understand their programming language and don't care to understand SQL, and also because they get away with it when there are 1000 cars in the fleet, and didn't anticipate having 1 million.

This is a problem that goes beyond the usual ORM antipatterns and bad database designs and becomes its own special kind of hell, because it makes code far harder to reason about and thus far harder to review, in a way that I don't remember encountering in Hibernate, Gorm, or anything else.

[0] count, length, and size

[1] includes, preload, and eager_load

1 - http://diesel.rs

Fancy a couple of jOOQ stickers? :)

And while I have you here...your blog posts are always great and very informative. It's interesting to see the differences between RDBMSs (I really only have experience with MSSQL/MYSQL some PG), and how they do one thing or another.

Will do! Thanks for the nice words about the blog.

I feel like this brings up an annoying cultural problem in tech wgere developers hit intermediate skill levels and feel the need to proclaim their expertise by writing something in the you’re-doing-it-wrong genre but don’t have the breadth of experience to realize that they’re over-generalizing and so they go from “ActiveRecord has this problem” to “ORMs are bad” without asking whether anyone else has it better. I’ve read tons of similar posts where e.g. Java proved that exceptions, OOP, or static typing were bad or Twitter proved you shouldn’t use Rails.

IMO ORMs should be simple, unsurprising, and only have one or two methods which actually execute the sql and fetch data - they are there to make it simpler to build queries and map rows to objects, not to blur the line between fetching and manipulating data.

They seem to spend solid third of their time googling for how to write a code. Write code, hit a jam, google, find something in stackoverflow, ruminate between what is found, copy paste and try if it works. They don't know their framework well enough to just write a code.

I'm a more of a embedded software/hardware guy and manager, but I can sit down and write pivot pivot query in plain sql without consulting any references.

I'm not saying these guys are doing something wrong. It may be that in the typical consultant work where you have existing old frameworks, new frameworks and new stuff coming in constantly, there is less value to master the tools that you are using. You write some glue, then move on.

You can differentiate the workforce. Programming by copy pasting example code withing frameworks may allow skipping the requirement to understand general algorithms.

Less required expertise means less pay and cheaper products. You hire 10 low-paid easily replaceable code monkeys who slap together pieces of software from ready components. It's like like assembly work. Then you hire one guy who knows things to supervise them and solve the problems when they can't figure them out. It's like blue collar assembly workers and engineers.

  SELECT * FROM DATA
  for each r in result
    if r.x > 12
      do_something(r.y)

None of their developers knew about WHERE constraints but they did know how to join tables so they were looping over hundreds of millions of rows in classic ASP using essentially the code you have above, with a bunch of unnecessary type conversions cargo culted into the inner loop (IIRC string to int to string).

Added business lesson: we billed double for a rush job but since it was only about 20 hours to fix the code and build out the remaining features (about half of the site), the original contractor still made considerably more. Our sales guy wasn’t canny enough to realize that he should have offered to do it for, say, a third of the other company's price.

  SELECT * FROM DATA WHERE x > 12

  // get list of ids
  for each id in ids
    r = SELECT * FROM DATA WHERE id=:id
    if r.x > 12
      do_something(r.y)

This was all dev 101 when I was coming up (yikes, almost 20 years ago now).

(You don't know the minutiae of pointer arithmetic!? You're not familiar with Docker, Vagrant, or AWS Lambda!? You can't construct a sed / awk one-liner with your eyes taped shut!? You don't know about concurrent skip lists!? You've never completed SICP or read Purely Functional Data Structures!? And so on.)

To be fair though, ORMs do move us one layer further away from the actual DB, so people tend to be even less likely to understand how to write ORM calls which result in performant SQL calls.

Additionally, the ORM makes it much harder to see what is going on under the hood. For example you don't know if a value of an object is stored in the main table or lazy loaded from a related table. So you have no clue if echo "$user.name lives in $user.city" results in 0 sql queries or one or two.

Again this doesn't match my experience. A mediocre .NET developer who can access the database with LINQ is far more likely to run their queries on the database than a mediorce .NET developer who has to try to write SQL queries by hand.

And the truth is that modern ORMs are often pretty clever with their optimizations, and in many cases the ORM will often outperform an average developer doing the obvious thing is SQL.

This can be done on SQL itself, but it would be much harder.

I've never in the last 20+ years seen an ORM that doesn't allow you to log the SQL queries with a single configuration option.

If you have no idea what you are doing with an orm you probably won't be able to write decent SQL anyways.

Not using an ORM requires writing lots of code to do what the ORM would otherwise do. Is that really appropriate today? Should we not take advantage of the available CPU/RAM? I say ORM is the correct choice in many cases. Not always.

    I say ORM is the correct choice in many cases

For simple queries though (single-table), Entity Framework is just as fast.

Because I’m mostly working on dashboards and stuff, writing to the database isn’t much of a concern.

    db.Query<Car>("SELECT * FROM Cars WHERE InsuranceEndDate < @Date", new { Date = DateTime.Now });

jOOQ. Not really an ORM, but handles the tedium of mapping results to an object while letting me write type checked SQL. IMO, it is the best solution for dealing with an RDBMS. I wish every language had a jOOQ equivalent.

Just as a reminder, Hibernate is 17 years old :).

Also, modern ORM frameworks give ways to iterate on the data from the programmers language, but translated directly to SQL. See what [Diesel](http://diesel.rs/) does, for instance.

The paper talks about other issues, which comes from negligence or a lack of understanding of the performance costs of the queries. But this performance impact could aslo happen in a SQL-only environment (not using a framework won't help you to create indexes, or to add pagination to your queries).

The ORM makes it easier to do things like Class.filter(insurance_end_date < today_date)

But hey what do I know, I am not berating the youf of today so I don’t belong in this skit.

    Class.filter(insurance_end_date < today_date)

Django has similar functionality through Q and F expressions.

Most ORMs have this functionality of specific language constructs to allow complex queries to be expressed without writing SQL, just with slightly different semantics.

You can ever traverse relations and still use the same syntax: https://docs.djangoproject.com/en/2.0/topics/db/queries/#loo...

     select count(*) from cars where condition;

Furthermore most ORMs (certainly any that I would consider using!) allow escaped SQL to be used - and if the query gets much more complicated than a couple of where clauses I consider using this feature.

A decent ORM used well allows programmers to program faster on the simple stuff but still write fast code for the complex stuff.

I think the article alludes to much more important problems like querying in a loop, querying the same information again just because it's not in function/object scope

A lot of the mistakes I see are because developers don't learn SQL, use it wrong, then assume it's slow, then use NoSQL, then reenforce each other into believing NoSQL is saving their performance

Let's not make this a conversation on ORM or not, but how do we make sure developers understand how to properly make use of their relational databases, which for the last decade or so has been painted as old & cruddy compared to sexy NoSQL

On top of the data/frameworks issue there's the one of data locality. Frameworks make the tradeoff decisions for you, but not always the right ones since the right tradeoffs are application-dependent. In one context using a cluster of commodity machines that do a mix of compute for your service and store and shuffle data around is a good idea. In another context a few beefy servers with the beef divided up between the app servers and database servers depending on where most of the compute most efficiently takes place is a better idea. (Though even with poor performance design, hardware is still good enough that a lot of things run just fine by having multiple services on one modest machine. That doesn't stop lots of programmers from reaching for frameworks that demand easily scaleable multi-machine systems even when there's no need and won't ever be need.)

In any case consistency matters: SQL strings are just one form of shipping code to the data to compute remotely instead of retrieving the data and computing locally. When you bring in an ORM, inconsistency is bound to follow with lots of computation that could have been in a query or stored procedure now living in the ORM framework and the application itself.

I can at least understand many people's annoyance with stored procedures though. Most DBs procedural extension languages suck badly enough that shipping JavaScript strings over to NoSQL stores can seem like a big improvement, and various levels of SQL standard conformance is all you typically get to use from the broader design pool of declarative languages.

If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still works. One advantage of an Orm is that if you keep your objects in line with your database the generated sql will stay correct.

Personally, I am more than happy to take that hit. I think it is a price well worth paying in order to have optimised queries that do exactly what I what them to do. To make it easier for myself though I will always try to keep my queries in one place in the code. Then all my code needs to know is it is getting clients_older_than(32) or whatever..

It maps 1:1 to the SQL statement that gets executed so there's no magic e.g. extra n+1 queries being introduced without you noticing.

But if you change your schema, re-generate, and immediate compile errors showing you where you're referencing something that's now been deleted, so it's not fragile like putting SQL in a String in your code (where if the schema changes, the compiler can't help you)

Intellij is pretty good at highlighting and pointing out typo errors. The code will still compile though.

> no typing information

Given the popularity of javascript, I thought this would now be considered a feature ;)

To me the line of argument around lacking static type checks always felt like FUD, but maybe it's best countered with counter-FUD...

If you've opened up your DB layer to accept strings (that you are supposed to build with a SqlBuilder statically referencing column and table names) you still have no guarantees that everything still works because some code somewhere might have just used a handwritten String instead. In other words you still need tests.

The static type checking value only helps against schema changes that rename or remove things, which is generally pretty rare, and depending on the level of autogeneration and query mapping might not even help if the column type changes. It doesn't help when the semantics change. For example (much more commonly) the introduction of a new column that is expected to be filtered on and/or required to be set a non-null value in inserts. So the static references are mainly reduced to being a mechanism to make finding users of a table easier, and hope that whoever is making the schema changes is going to look around for those users and update them accordingly. (When you don't own the table, as is usually the case in large software with many teams, the table owner similarly doesn't own your code, so that's kind of a vain hope. The best solution is what is done in open code with unknown consumers -- versioning. Stop renaming/deleting/changing the semantics of things, just provide new things, under different versions or namespaces if they really need to share names.) But you can accomplish this to the same effectiveness by creating a static reference to the table near where you execute queries on it, and use easy to read handwritten strings for the rest. In my experience though many queries are trivial enough that a SqlBuilder-esque pattern isn't much overhead, it's not a hard hit most of the time, and more complicated ones may belong as stored procedures if you've already invested in that direction.

If you go the full ORM route, which it sounds like you aren't suggesting since you mention optimized queries that do exactly what you tell them to do, the table owner may graciously update the central object builder to set a non-null default value for everyone (or specify one in the table def), which would maybe stop things from breaking immediately, but maybe wouldn't actually stop breakages (especially on the select side where data is retrieved that under the new semantics was meant to be filtered), so end users are still on their own for whether this new column matters to them or not. And that's just one type of schema change that's not a simple rename or column removal, there are many others.

The solution becomes really obvious if it happens once for 95% of people good at computers.

You might regret it a bit more if you do something like a filesystem read to get the query.

There's your regret. It hurts so much, doesn't it? :)

In Smalltalk, you would write such a query as:

   (cars select: [ :car | car insurance endDate < now ]) size.

So what Gemstone does is that when cars is a DB collection, it interprets the block and turns it into a query. So the code for iterating in-memory and the code for doing an optimized query is the same.

    DB[:cars].where { insurance_end_date < Time.now }.count

I think most ORMs can do a reasonable job at this in languages where you have sufficient flexibility in overloading behaviour.

The places I've worked at that have written true Enterprise software, whether consulting, publicly traded healthcare companies, banks etc, have had extremely knowledgeable DBAs writing stored procedures for data access. The ORM usage was reserved for modifying the results of stored procedures or small things like account management. Nothing truly critical to the business was handled through an ORM directly pulling data from tables.

As for performance, note that even when you know exactly what you're doing it is hard to wring good performance out of relational databases. This is why companies spend hundreds of thousands of dollars a year to hire "Database Administrators" to design and "tune" their database. (What other piece of software requires a highly paid, full time expert?) The most complicated databases are so complicated that we get "Junior DBAs" and "Senior DBAs" and multiple levels of certification.

And let's not forget what happens at extreme scales. At the lowest latencies and the largest data sets relational dbs are simply impossible to use. This might never be a problem for most businesses who are processing a few hundred messages a second (if that) but it should be on the mind of any startup that hopes to one day have millions of customers.

In the long run memory will become cheaper, faster and persistent. (Let us pray.) When that happens most everybody will abandon the big mess that are relational databases and just manipulate objects in memory as the gods intended. Then the relational model just becomes something to scare grand kids with.

It is possible i was overly harsh in my judgement, as assumed meant no real familiarity at all.

We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.

You could replace everything you said with using SQL directly and just doing a select * from cars