3 ms·

The correct answer to almost every db question is: “it depends”. For instance. “Select *” can be good for ad-hoc exploration (as long as you include a limit to

The correct answer to almost every db question is: “it depends”. For instance. “Select *” can be good for ad-hoc exploration (as long as you include a limit to the rows returned). It’s bad practice for shipped code because it can lead to binding issues, in code, when you modify or add columns. “Select *” will almost certainly be optimized away by the execution plan but if you have blob data, could introduce a lot of unnecessary disk i/o. Is “select *” bad? It depends.

sk5t 6y ago
> “Select ” will almost certainly be optimized away by the execution plan What do you mean? Ask for all the columns, you're gonna get all the columns in a resultset. Only in a subquery could the planner know what is unused. 'select ' is _perhaps_ tolerable for creating a view--under the conditions that the database transforms the query into the unambiguous named columns at view creation time--and that's about it.
- pfarrell 6y ago
  You’re right. When I wrote that, I was thinking higher up the chain in an orm layer. Sending “select *” to the db can’t be “optimized away” at that layer, it will have to return all the columns. Unless, like you said, the scope of the “select *” contains it in a sub query where the columns used can be known... “it depends :)”
bitexploder 6y ago
And, not to be a curmudgeon, but, most apps don't really need that level of optimization. In 90% of apps it's fine. The 10% that really need the performance are hopefully doing it right with well tuned indices, careful denormalization, and other things. It is unimaginable to me, that someone with a higher performance SQL/RDBMS backed app can't run a query planner and tame ORMs for performance and identify things like wildcard column select. But what do I know :)
- pfarrell 6y ago
  Very true. Also, along those lines, if you have fewer than 10k rows, it’s likely the dB will keep the whole table in memory after the first access, anyway.
  - jimsmart 6y ago
    In an ideal situation, agreed. But surely that depends entirely on how many tables are being queried (how many tables already exist in the cache), the size of the rows, how much RAM the host has / how big the cache is, plus a few other variables most probably. What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.
  - skeeter2020 6y ago
    he's not really talking about the performance of the DB though, focusing more on size of the resultant payload thatr gets sent over the wire. In the actual database which is traditionally very challenging to scale, projections aren't very expensive. The other things you and GP mention are way more likkely to move the performance needle.
skeeter2020 6y ago
>> binding issues, in code, when you modify or add columns. This is very common IME, and it is a very bad code smell. It tends to break things very low in the architecture, so even capturing the error makes the application essentially not work in a fundamental way.

sk5t 6y ago

> “Select ” will almost certainly be optimized away by the execution plan What do you mean? Ask for all the columns, you're gonna get all the columns in a resultset. Only in a subquery could the planner know what is unused. 'select ' is _perhaps_ tolerable for creating a view--under the conditions that the database transforms the query into the unambiguous named columns at view creation time--and that's about it.

pfarrell 6y ago
You’re right. When I wrote that, I was thinking higher up the chain in an orm layer. Sending “select *” to the db can’t be “optimized away” at that layer, it will have to return all the columns. Unless, like you said, the scope of the “select *” contains it in a sub query where the columns used can be known... “it depends :)”

bitexploder 6y ago

And, not to be a curmudgeon, but, most apps don't really need that level of optimization. In 90% of apps it's fine. The 10% that really need the performance are hopefully doing it right with well tuned indices, careful denormalization, and other things. It is unimaginable to me, that someone with a higher performance SQL/RDBMS backed app can't run a query planner and tame ORMs for performance and identify things like wildcard column select. But what do I know :)

pfarrell 6y ago
Very true. Also, along those lines, if you have fewer than 10k rows, it’s likely the dB will keep the whole table in memory after the first access, anyway.
- jimsmart 6y ago
  In an ideal situation, agreed. But surely that depends entirely on how many tables are being queried (how many tables already exist in the cache), the size of the rows, how much RAM the host has / how big the cache is, plus a few other variables most probably. What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.
- skeeter2020 6y ago
  he's not really talking about the performance of the DB though, focusing more on size of the resultant payload thatr gets sent over the wire. In the actual database which is traditionally very challenging to scale, projections aren't very expensive. The other things you and GP mention are way more likkely to move the performance needle.

skeeter2020 6y ago

>> binding issues, in code, when you modify or add columns. This is very common IME, and it is a very bad code smell. It tends to break things very low in the architecture, so even capturing the error makes the application essentially not work in a fundamental way.