7 ms·
> assuming that your application doesn’t actually need all the columns. Title is a bit click-bait-ish; I was expecting some deep insight of why this: SEL
by bjarneh 6y ago
> assuming that your application doesn’t actually need all the columns.
Title is a bit click-bait-ish; I was expecting some deep insight of why this:
SELECT A,B,C FROM TBL;
was superior to this:
SELECT * FROM TBL;
when the table had three columns. Instead we get a wildcards are bad argument; especially when that wildcard fetches data that you don't need etc; which I guess everyone agrees with.
- Hjfrf 6y agoYour first question is fairly common too- select * is to be avoided if your table can change or you care about order of columns. Select * is better from cte/temp table because you then only need to make changes in one place. I'm not sure how much deeper it's possible to go.
- GekkePrutser 6y agoI agree wildcards are better avoided but so are column orders in result extraction. They are a source of bugs and much review work when changes are made. If I had to choose (which usually isn't necessary) I'd say wildcards are the lesser evil.
- 867-5309 6y agoit's becoming increasingly more common to sort columns at the application layer
- rbut 6y agoI agree. Applications that rely on the ordering of columns are as bad as people who complain when I add a new column into, or change the ordering of a CSV which has column headers. There is no technical reason to do this; computers are ample fast enough to look up columns by name!
- csharptwdec19 6y ago>computers are ample fast enough to look up columns by name! Sure, but depending on number of records and/or implementation of the name lookup, it can add up over a large resultset, or have a slightly cost over many smaller queries. Mind you, I think -most- implementations are smart enough to only need to do the lookup once for a returned datareader, but the cost is still there.
- nkozyra 6y ago> so are column orders in result extraction. While I agree, there are some languages + db drivers where this is your only choice unless you roll your own. Go's sql package relies on order in the Scan method, for example.
- Pxtl 6y ago"care about the order of your columns" is a far bigger red flag than "select *".
- HenryBemis 6y agoHi, IT auditor here. Sometimes 'we' ask * instead of "gimme everything that starts with a number" because we may need to check that the column "ID" which is supposed to be a 10-digit number, may have an entry "A1234567890". And this has not raised any alarms. And this means that this human is not paying taxes, because his/her tax-ID is not 'run' when the gov runs the tax calculations, because the script pulls 10-digit numbers, and my ID has a letter. So some DBA got paid $50k, changed my field, run the thing, change back my field. If you think this is not happening, it is. Consider other scenarios on obligations like that. I once did a KYC audit for a bank. When I asked * , I laughed and cried with the results. Soooooooooo.. yes, very click bait-y title. Each SELECT should fit the purpose it serves for the report reader. When doing KYC and you have to review 5mil clients, you want *, otherwise you will miss the: Surname: 123Smith!!!, DoB: 30/2/1980, and other duplicates, nasty surprises, people over 150yo, etc.
- hnlmorg 6y agoUnless I'm misunderstanding, what you're talking about there isn't wildcard selects but rather SELECT's without any filtering. ie having `SELECT id, firstname, surname FROM people` would still catch 'A1234567890' and '123Smith!!!' Where as `SELECT * from people WHERE id REGEXP '^[0-9]+$'` would exclude them despite being a wildcard SELECT.
- astura 6y agoUmmm... you seem very confused... The "select" part of the query solely filters on columns, not rows. You are talking about filtering on rows. Beyond that, when you are looking for anomalous conditions you don't say "get me all records," because how you going to pick out something that doesn't look right out of five million items? When you are looking for anomalous conditions you say "get me all records that are NOT in the form I am expecting." Then you add constraints, foreign keys, etc so it doesn't happen again.
- whack 6y ago#1 comment: > Instead we get a wildcards are bad argument; especially when that wildcard fetches data that you don't need etc; which I guess everyone agrees with. #2 comment: > Nevertheless in reality most of the stated reasons have almost no real practicability... So in most cases fetching to many columns would be no problem This happens surprisingly frequently in a lot of tech discussions. The most popular comment accuses the author of saying something so obviously correct, that it's a waste of time to even bring it up. And the second most popular comment is someone telling the author that he's wrong.
- RobRivera 6y agoit does not invalidate his/her observation
- Semaphor 6y agoBoth comments can work together. Yes, doing more work than necessary is bad (#1). The amount of "bad" is very low to barely measurable in most real-life scenarios (#2).
- vp8989 6y ago"The amount of "bad" is very low to barely measurable in most real-life scenarios (#2)." When you constantly apply this "only things bigger than X matter" reasoning you still end up with slow, shitty software but you just died by a thousand cuts instead of 1.
- VBprogrammer 6y agoAnd the rub is if you fail to apply it liberally enough you end up with no completed software or difficult to maintain software because someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be.
- vp8989 6y ago"someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be" This feels like a strawman. I have yet to see an instance of this in my 10 years. The much, much, much more common case is people justifying half-assed shoddy work with YAGNI.
- ComodoHacker 6y ago>which I guess everyone agrees with. You would be surprised how many don't even think a second about it, not to say realize any of the points made by the author.
- actuator 6y agoI 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 agoThat'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 agoI 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 agoThe 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+).
- tw04 6y agoIn general, in my limited experience, the former is better than the latter because you have NO IDEA what's going to be in that database a decade from now, but your query is probably still getting called by some obscure application that nobody even knows exists. When someone does something stupid and puts a whole bunch of unexpected data into that database, the latter query will cripple the entire show, whereas in theory the former will still just ask for the original intended data.
- crazygringo 6y agoReally? In my experience adding columns to a production database table is pretty infrequent, and even when it happens it's usually only one or two. (It's often a pretty big headache to add columns in practice.) I've worked on a lot of databases and I've never seen the number of columns grow to the extent that it would impact performance in a large way. It might go from 8 to 11 columns, but I've never experienced anything like 5 to 55. For a variety of reasons, if there's a whole new set of data (like customer survey responses to be associated with the customers table), or large data (like product thumbnails to be associated with the products table), to be associated with primary keys, it's added to an entirely new table that you can perform a JOIN on only when required. Not to mention that while any application client can often add new rows to a database, they can't generally add new columns -- whoever administers the database often keeps those privileges for themselves for general security reasons.
- rm_-rf_slash 6y agoCould also be a matter of the column content. It may just be one json column added to your Postgres table, but who knows how much space one row could take in production.
- ericbarrett 6y agoI worked on the Facebook MySQL team for a few years. SELECT * was banned for exactly the reason GP stated. One scenario that can really bite you here is having a column of sub-structured data, like a JSON struct, added to an existing table of “skinny” data: imagine your schema is (id, first, last, phone, email) and you add a bio_as_html column. Now your SELECT * is returning a potentially large, opaque string blob for every row, to no purpose. This wastes network bandwidth, CPU, heap pressure, cache pressure, and so on. Another reason—if you have multiple databases (or just non-synchronous replicas) and do online schema changes, it’s possible to end up with different schemas visible on the same table. If you are confident that every engineer in your employ is an experienced SQL author and/or your ORM is perfect and would never access a column by index number rather than text name, you should be OK. Everybody else should query explicit column names.
- bcrosby95 6y agoDepends upon how you use your database. If tables are a 1-to-1 representation of domain objects then you should use select *. If it isn't then you should prefer to be choosy about your columns.
- dathinab 6y ago> [...] > was superior to this: > [...] For API Stability: - querying only the columns you really need makes it less likely that a column you don't really need happens to somehow brake your code if changed (e.g. because some ORM mapping not working). (This btw. is one of the major drawbacks of many REST APIs, i.e. they default to the rest equivalent of ). - deciding the order in which you want columns to be returned explicitly is less bug prone to a variety of bugs, relying on column order of `` is brittle but some ORM do so and some other code might do so by accident in some way or another. Explicitly defining the order makes it easier to avoid such bugs. - SQL queries doesn't need to be updated if you add columns (to explicitly not query the for the query unnecessary new column)
- brundolf 6y agoIt's obvious once you think about it, but it's not something I'd ever thought about before. I think the article still had value