Things I wished more developers knew about databases

Re:Things I wished more developers knew about databases by btilly on Wednesday April 22, 2020 @11:22AM (#22946506)

If you're writing a CRUD application, an ORM saves a lot of headaches.

If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong.

As an example, very, very few ORMs can make the distinction between

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
          AND bar.category_id = 5
      LEFT JOIN baz
        ON bar.id = baz.bar_id
    ...

and

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
      LEFT JOIN baz
        ON bar.id = baz.bar_id
          AND bar.category_id = 5
    ...

(And if you get into things like analytic queries, just forget about it.)

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by jakearmitage on Wednesday April 22, 2020 @11:33AM (#22946647)

Curious, what is the difference here?

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by saltcured on Wednesday April 22, 2020 @01:09PM (#22947792)

The extra "AND expr" in the join clause makes it more strict, which means the outer join can produce more nulls in the joined tuple. In a sequence like A left join B left join C, moving the extra join filter between the two joins is the difference between getting tuples like (a..., null..., null...) vs (a..., b..., null...).

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by gtsteve on Wednesday April 22, 2020 @12:36PM (#22947404)

I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs.

If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it.

This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL.

I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by btilly on Wednesday April 22, 2020 @12:42PM (#22947481)

No, left joins were intended here. They mean different things.

With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by gtsteve on Thursday April 23, 2020 @07:10AM (#22954709)

I see, apologies.

However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by btilly on Thursday April 23, 2020 @05:06PM (#22961429)

I can't say that MySQL doesn't perform poorly. But if so, then their optimizer is more broken than I though.

Even SQLite figures this one out and does it right.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by btilly on Wednesday April 22, 2020 @12:41PM (#22947456)

The difference is whether the condition on bar is a filter is on the join from bar or baz.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by magicalhippo on Friday April 24, 2020 @09:41AM (#22967813)

In the first case, if for a given row bar.category_id <> 5 then there's no match and hence null will be returned for any value from bar. Thus there will be no match in baz either (assuming the id columns are non-null).

In the second case, the first join will always return a row from bar for a given foo_id if it exists, but the second join does not return values from baz for rows where bar.category_id <> 5.

[ Parent | on HN ]