Hacker News Reader

Comment by lmm | Hacker News Reader

It's extremely easy to write SQL queries that take a lot longer than they look like - an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different. So as far as I can see writing SQL manually is just extra drudgery for no real gain.

View on HN

I don't know.

Hand-written SQL means a bunch of work and you have to know SQL. But it is simple and transparent.

ORMs automate a lot of simple and repetitive SQL, but to use them effectively you really have to know SQL extremely well and understand the ORM deeply as well.

So I guess it depends on what you are doing. ORMs can be useful but they require a lot more knowledge to use effectively than hand-coded SQL does.

> ORMs automate a lot of simple and repetitive SQL, but to use them effectively you really have to know SQL extremely well and understand the ORM deeply as well.

I'm not sure. You need to know the relational model pretty well, but you don't have to remember the zillions of quirks and edge cases or differences between dialects that SQL has. IME that's what takes most of the memorization effort.

> an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different

ORM queries compile down to SQL, so how would they look different?

Typescript compiles down to Javascript but an unchecked cast looks different from a known-safe assignment in Typescript even though they look the same in Javascript.

We're talking about something that exists at the query planner level, not a new feature introduced on top of that. Whether to use an index or a table scan isn't chosen by the user, it's chosen by the engine when the SQL is run. ORMs don't have any special hook to "look different" - if the database engine wants to do a full table scan, it'll do it whether the query came from manual SQL or an ORM, because they look the same to the database.

Depending on the database, the ORM may well add hints for the planner (of course it's theoretically possible to do that for manual queries as well, but it's usually verbose and database-specific). At a minimum the ORM has a model of what indices exist and so it can know which queries "should" be using an index, and may even have an internal model of how the query planner makes those decisions; of course ultimately if the query planner is perverse enough then even an automated system may not be able to get it to do the right thing.

Reply on news.ycombinator.com