Hacker News Reader

Comment by actuator | Hacker News Reader

I think the title is apt. It is quite common for people to mistakingly do this pattern, I have seen this mostly happen through ORMs.

It might have changed but in ActiveRecord it was fairly common to do.

  models = Model.where(column_name: column_val)

and then further down the fetch, just use two three columns from the whole fetch. This used to issue a `SELECT *` to the table if no select clause went along, and most people don't realise the harm with fetching all columns. Even if it is a single row, storage engines like InnoDB store blobs in an indirected way, which can lead to more read IO ops than required.

View on HN

That's not a mistake at all. It's the simplest code that loads the data you want. 99.9% of cases the "harm" is insignificant. Anything more complicated is premature optimization.

I think using SELECT in all cases, enforces the idea of just requiring what you need which just gives the performance benefit in other cases even if they occur less.

Even without the optimisation bit, personally I prefer when code is explicit about what it is doing, like the data it is operating on/with. While omitting select makes the code succinct, having it there gives an idea at a glance about what it is operating with.

The real landmine underneath all this is: what is going to be updated when.

In all probability (we did say Oracle, which means enterprise), this application code is going to live forever, and never receive dev attention once it reaches functional status (unless something breaks).

Consequently, encoding something like "SELECT *" on the app side means that will never be updated, or even noticed, which is the same as saying it will be impossible to subsequently optimize.

Because you can't optimize specifics (these columns) from the DB side, when the only information an application gives you is generic (requests all columns).

Multiple this by 100 apps hitting the same core databases, and you start to see why it's a big deal, from an organization-lifecycle perspective (over 20 years+).

There is no optimization to which you cannot apply that logic. You're claiming "always optimize everything, because we can never change code". Even in enterprise, this is nonsense.

This is classic premature optimization. You don't know that overselecting will have any significant performance impact, and barring a few obvious cases (LOBs) it's quite unlikely to matter. There's a material cost in complexity to add situation-specific SQL vs the aforementioned:

    models = Model.where(column_name: column_val)

Most databases make it pretty easy to find out what queries are consuming resources. Spend your time optimizing those.

There are tons of optimizations that logic can't be applied to!

For starters, any software developed in any company with the ability to deliver updates in a timely manner.

It's a tradeoff. You might want to encapsulate database access and deal with objects instead of relations in most of your stack, which leads to these inefficient access patterns. Usually it doesn't matter. And if it does matter, you are probably doing something harmful like querying in a loop, n+1 etc.

Reply on news.ycombinator.com