Hacker News Reader

Comment by dagw | Hacker News Reader

That has nothing to do with ORMs. I've seen plenty of people write code like:

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

Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.

View on HN

My record was a 5 order of magnitude performance improvement on a site which a big consulting firm had been working on for most of a year, where page rendering times were somewhere north of 20 minutes (the server’s hard timeout).

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.

Hey, I think those same consultants worked on the main website for a company I used to work for :)

Seeing how much business people like that got was definitely an educational moment about the efficiency of the IT market ;-)

I've also seen:

  SELECT * FROM DATA WHERE x > 12

where there's no corresponding index. There's also the infamous N+1 query pattern:

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

IMHO, both are signs of not fully understanding what the database does for you. In the same vein as the "learn JS before frameworks" argument, I'd argue devs should at least learn and understand SQL, indices, etc. before using ORMs.

> I'd argue devs should at least learn and understand SQL, indices, etc.

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

Yeah, I wish it were common knowledge...although, if you ask 10 different devs, you'll get 10 different answers on what "ought" to be included in a basic CS / developer education.

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

I agree that this has more to do with the developer than the ORM. I once watched someone write a foreach loop around a set of millions of records, to update their "BatchDate" property to the current date / time. To add insult to injury, to get the current time, they issued a separate query to the DB, in the form of "SELECT GETDATE()", then injected the result - as a string - into the update statement.

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.

The ORM makes it more likely to do stuff in the application instead of the DB because many queries are hard or impossible to represent in an ORM fashion.

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.

> ORM makes it much harder to see what is going on under the hood

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.

Heck, I'm disappointed in any ORM that doesn't log queries by default in development mode!

With an add on Django even displays them in the browser!

It's trivial to find optimizations in django debug toolbar..."oh yeah I should use select_related"

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

This is only true if you let the ORM design the schema for you. I would hazard a guess that most people only use ORMs for queries (including mutating ones). In which case you very much do know which table a field is in.

Even if you use the ORM to design the schema for you, an ORM isn't going to just willy nilly put fields in tables by some confusing whim. Even the most opaque ORM conventions for field naming tend to have relatively easy ways to spot that MyClass.propertyName binds to table my_class and field property_name, or whatever is the case.

The ORM makes it more likely to do stuff in the application instead of the DB

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.

Also ORMs have enough visibility into your code to tell you of for doing stupid things. There are libraries working with ActiveRecord (and likely others too) which will warn you that you could skip the specific query if you added .eager_load(). Or about 1+N you're doing needlessly.

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

ActiveRecord has a find_by_sql which I use for medium to complex queries. They are easier to write and understand in SQL than in Ruby. The problem is that there are many developers that don't know SQL. I found them in Ruby projects and Python and Node.js. They write whatever they manage to code with the ORM and don't understand the implications on database performance.

Reply on news.ycombinator.com