5 ms·

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

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.

stickfigure 6y ago
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.
- actuator 6y ago
  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.
  - ethbr0 6y ago
    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+).
    - stickfigure 6y ago
      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.
      - ethbr0 6y ago
        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.
thomasz 6y ago
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.

stickfigure 6y ago

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.

actuator 6y ago
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.
- ethbr0 6y ago
  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+).
  - stickfigure 6y ago
    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.
    - ethbr0 6y ago
      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.

thomasz 6y ago

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.