Hacker News Reader

Comment by pydry | Hacker News Reader

>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization

I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group.

You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize.

There is also a small % of queries which use some feature of your database engine which the ORM won't support.

View on HN

I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records.

I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple.

> It's also very hard if not impossible to guess which queries will end up in that group. > You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize.

I disagree on this. It's almost no extra work to just write these optimised in the first place (if you're not trying to squash everything into an ORM workflow). So it makes sense just to write them all optimised. Ditto for doing batch inserts and updates rather than looped inserts/updates (although you can usually use the ORM for this).

> It's almost no extra work to just write these optimised in the first place

Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time.

Writing queries that are optimized in the first place just means now you have to maintain a bunch of SQL and you have to rely on anyone modifying that SQL later understanding those optimizations. Sometimes it's necessary, but if it's not, I think it's much nicer to stay in ORM-land even if the query might not be optimal.

> Not everyone is capable of quickly optimizing SQL

I mean that's true, but equally not everyone is capable of using an ORM. I don't think SQL is inherently any harder to learn.

At my last job, I had juniors who had never used SQL at all productive in SQL within a couple of weeks, and using "complex" SQL like JSON aggregation and windows function with a few months. They were a little intimidated by it when they started, but didn't find it too hard to learn in the end.

Honestly, I think I could get someone with no raw SQL experience writing code that’s at least 50% faster within a few hours. There are so many footguns that ORMs completely ignore, and never warn you about.

Using a case-sensitive filter (default for Django) in a DB with case-sensitive collation (default in Postgres)? Django will helpfully cast the tuple and your query to UPPER to match it for you, and the former wrecks indexing.

Checking if a string ends with something else? Goodbye, index.

I _think_ the latter can be worked around in PG with a GIN index, but I’m not positive (I work with MySQL much more). And in any case, you’d have to know to create that, and I imagine most devs won’t.

Fixing seemingly tiny things like that have a massive impact on large table query speed.

With your example of optimization, I think anyone who is able to make this optimization could certainly also implement it on top of an ORM.

>I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick.

What is it that you do here that can't be handled by, say, django's workhorses - filter and select_related?

If it's impossible to write 90% of your queries in an ORM my suspicion would be that you're either not using the ORM correctly or you're using a crappy ORM.

I'm unfamiliar with Django's ORM specifically. But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable. Things like:

- Complex joins

- Complex WHERE clauses with mixes of AND and OR (with parentheses)

- JSON aggregation

- Window functions

tend to require quite heavyweight syntax in ORMs (e.g. nested lambda functions). Whereas the corresponding SQL tends to introduce much less noise.

It's basically just another case of a dedicated language being nicer to use than a DSL embedded into a general purpose language. Normally it's not worth creating a whole language just for nicer syntax, but in the case of SQL the language already exists! So why not use it.

>But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable.

This is the problem with not using an ORM. If you cut it out and move everything to parameterized SQL queries the SLOC explodes which massively inhibits readability as well as introducing bugs.

If your issue with ORMs is just that you're familiar with SQL and you don't like how ORMs look then I think the issue is just about becoming more familiar with a decent ORM.

> If you cut it out and move everything to parameterized SQL queries the SLOC explodes

My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.

I have never seen an ORM in my life that didn't reduce the total amount of code written. Not even the Java monstrosities increased the SLOC.

Perhaps it depends what you're doing?

IMO:

    .where('column_a', '=', 'value1')
    .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2')))

is a lot less readable than:

    WHERE
       column_a = 'value1'
       AND (column_b IS NULL OR column_b = 'value2')

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2'))

And obviously you can use whatever indentations you like.

Ah, that's quite a bit nicer. You can't do that in JavaScript on two counts:

- No keyword arguments

- No operator overloading (so you can't override | to get the nice "or" syntax)

I actually messed it up a little because I'm not sure you can mix positional and kwargs in filter, and you definitely can't use kwargs first. Still, the idea is there.

In JS, theoretically you could design an API like

.where({column_a: 1}, Q(column_b__isnull=True).or({column_b: 2}))

Which really isn't bad IMO

It's been a bit since I've used Django, but I believe you can just swap the order so the kwarg comes last.

    filter(     Q(column_b__isnull=True)|Q(column_b='value2'),
      column_a='value1',
    )

Or just turn it into another Q

    .filter(Q(column_a='value1'), Q(column_b__isnull=True)|Q(column_b='value2'))

The docs show both of those as working examples, but Q supports using & for "and", and can be combined in any way - you could also do this:

  .filter(Q(column_a='value1') & (Q(column_b__isnull=True) | Q(column_b='value2')))

One of these my IDE can typecheck and apply code hightlighting, the other is just a blob of text.

On my case that would be SQL, as I use nice SQL aware IDEs for Oracle and SQL Server.

Separate chain for a different Django example, it only natively supports joins on explicit foreign keys, but because it has that extra information in the model the syntax for using it is extremely compressed. Let's say you have a "Dog" table with a foreign key to "Owner", and "Owner" has a foreign key to "City". Getting all the dogs that live in New York would be:

   Dog.objects.filter(owner__city__name = 'New York')

The double-underscore follows the foreign keys until the last one, which is a field on the last model.

It's a ton of extra work. It's like 10x slower than using the Django ORM. Let's say it takes you ten seconds to write that query. I wrote it in one second with the ORM and my IDE.

That adds up, with almost no downside most of the time.

The problem is that what you typically get is one group of people who have no idea how to do anything outside of the ORM and play off the problems because hey, its only a problem query or two that use up 100% of the system resources of the databases and bring them crashing down, but might as well throw more system resources at it because we've spent no time understanding queries the last N years of building.

Or you use something much simpler and more light weight, like query builders or a tool that generates code from sql queries.

This approach is more bottom up. You end up with uni directional data flow, better separation of concerns, data coupling instead of object dependencies and better performance right out of the bat.

The cost? In my experience just some basic familiarity with SQL.

Nah, ORMs just encourage a lot of bad behavior, and come with edge cases and code bloat. You're better off using an API generator such as postgrest/hasura for the simple cases, and hand crafted queries for anything more complex than basic crud.

Reply on news.ycombinator.com