Re:Why SELECT * is bad for SQL performance (2020)
by stickfigure on Monday February 15, 2021 @03:26PM
(#26146897)
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)
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+).