Why SELECT * is bad for SQL performance (2020)(tanelpoder.com)
tanelpoder.com
Why SELECT * is bad for SQL performance (2020)
https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/
173 comments
#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.
> 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.
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.
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.
> “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.
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.
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 :)”
>> 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.
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.
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 :)
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.
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.
What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.
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.
Building software is making a series of trade-offs, but most tech articles present themselves as though they have the answer. It's not too surprising, then, that the top comments take opposing stances on an issue that is definitely more complicated than the article made it sound.
We have trade-offs but they are not all equal. It is both legitimate and encouraged to argue about which are the correct trade-offs IMO.
Agreed! The fact that the top two comments usually disagree is one of the primary reasons I read comments on HN. The dialectic allows me to make an informed decision later.
> accuses the author of saying something so obviously correct
Perhaps I do; but my main "accusation" here is that the title is pure click bait, if it was replaced by this:
Perhaps I do; but my main "accusation" here is that the title is pure click bait, if it was replaced by this:
SQL - Don't fetch data you don't need!
This reminds me of a chilling short story I read a while back, but I cannot remember its name. It was about a machine learning algorithm trained on social network archives to take as input a body of dialogue between people and communities and to output statements which would strongly divide them. As a joke, the developers ran the algorithm on their internal corporate email and read the output sentences, laughing that anybody at their company could possibly come down on the other side of any of the resulting statements before realizing that they were talking about different sides. No longer being able to stand each other, the company dissolves. It was a good story if anyone recalls its name.
I just read it again recently. Shiri’s Scissor.
https://slatestarcodex.com/2018/10/30/sort-by-controversial/
https://slatestarcodex.com/2018/10/30/sort-by-controversial/
That was a hell of a read. Thanks for the link!
That's it! Thanks so much.
Using select * can cause some correctness issues, but from a performance perspective, it’s basically an archetypal topic for bike shedding. In the sense that using the smallest projection you actually need is an optimization, but for most use cases it will end up being an irrelevant optimization. So select * is bad for performance, but you don’t need to care about it unless you’re experiencing one of the problems it case cause, which most people probably don’t. But you can have a back and forth discussions about that forever.
Well, the good news is that this comment cracked me up, thus demonstrating that this thread does provide some value to HN.
Cheers
Cheers
This is because "strong opinions, lightly held" creates a culture where people with little to no insight or experience feel empowered to create an unceasing high-volume firehose of worthless opinions that drown out anything useful.
it does not invalidate his/her observation
Both 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).
"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.
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.
And 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.
"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.
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.
Survivorship bias suggests we're more likely to see projects that resulted in released software...
See: solo indie gamedevs that build their own engines for years and subsequently fail to release anything.
Fortunately, stars in sql are also really bad for maintenance ;-) So I guess there is indeed no reason to use them!
I work that way, and I don't end up slow, shitty software.
I do end up shipping working software much sooner than if I optimized every statement everywhere.
I do end up shipping working software much sooner than if I optimized every statement everywhere.
In 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.
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.
Really? 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.
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.
I 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.
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.
[deleted]
Could 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.
It may just be one json column added to your Postgres table, but who knows how much space one row could take in production.
> In my experience adding columns to a production database table is pretty infrequent,
It's infrequent because crappy development practises mean that when you try and change the schema a pile of people scream abuse at you because their crappy wildcard queries break, because the crappy code written around those wildcard queries shits the bed if the columns change in any way, shape, or form.
It's infrequent because crappy development practises mean that when you try and change the schema a pile of people scream abuse at you because their crappy wildcard queries break, because the crappy code written around those wildcard queries shits the bed if the columns change in any way, shape, or form.
I was thinking more that the process can take hours with large tables (and even longer with replication), you have to be careful it doesn't lock your tables, and basically just a ton of edge cases.
"Crappy wildcard queries" would seem to be the least of it, ha. Most of the time, a wildcard query that returns additional columns at the end won't cause any problems at all -- client software will just ignore them. Unless you're making each row 100x larger in size and you get a performance hit, but that's the point I was making -- that's not something that usually happens in reality anyways.
"Crappy wildcard queries" would seem to be the least of it, ha. Most of the time, a wildcard query that returns additional columns at the end won't cause any problems at all -- client software will just ignore them. Unless you're making each row 100x larger in size and you get a performance hit, but that's the point I was making -- that's not something that usually happens in reality anyways.
Some columns have large size variance. You are hedging against them growing. It's almost never a problem on fixed size columns.
Yes, and this is the biggest non-performance reason that wildcards are bad: because over time, the schema should be able to change, often in ways that render select * (and the equally works-only-by-accident code calling it) broken.
This is even more so when joins or views are involved, because the columns returned may change over time; joins particularly. Or, for that matter, migrations; while a CREATE plus ALTER may have resulting in a particular column ordering in 2002-2017, the new DB using the CREATE that rolls up all those changes in 2018 may result in a different column ordering, with subsequent broken code (which will be blamed on the DB by the thoughtless programmer, of course).
This is even more so when joins or views are involved, because the columns returned may change over time; joins particularly. Or, for that matter, migrations; while a CREATE plus ALTER may have resulting in a particular column ordering in 2002-2017, the new DB using the CREATE that rolls up all those changes in 2018 may result in a different column ordering, with subsequent broken code (which will be blamed on the DB by the thoughtless programmer, of course).
That's fair, but some database systems have added functionality to avoid this specific problem. In Oracle, MySQL, and MariaDB, you can mark a column as INVISIBLE to exclude it from SELECT *.
It's still not a great practice to use SELECT *, but at least this gives you a way to add columns without breaking existing applications. It's also useful when you plan to remove a column, to test in advance whether anything using SELECT * would break without that column there.
It's still not a great practice to use SELECT *, but at least this gives you a way to add columns without breaking existing applications. It's also useful when you plan to remove a column, to test in advance whether anything using SELECT * would break without that column there.
That's an interesting feature and could be helpful for varbinary(max) type columns. But also very annoying because I'd be thinking "Does that column exist or not? Why is it not showing up for me?"
Your 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.
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.
"care about the order of your columns" is a far bigger red flag than "select *".
I 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.
If I had to choose (which usually isn't necessary) I'd say wildcards are the lesser evil.
I 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!
>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.
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.
> 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.
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.
it's becoming increasingly more common to sort columns at the application layer
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.
It might have changed but in ActiveRecord it was fairly common to do.
models = Model.where(column_name: column_val)
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.
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+).
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:
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)
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.
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.
>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.
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.
> [...]
> 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)
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)
Depends 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.
It'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
Hi, 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.
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.
Ummm... 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.
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.
Unless 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.
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.
These arguments are correct and repeated for many many years. Nevertheless in reality most of the stated reasons have almost no real practicability.
They re correct in an "academic view" but most applications are CRUD and based on an ORM and there are not many columns fetched too much which will make any difference. There are some rare cases where these statements are correct, especially the lob fetching but in these cases most often the queries are switched to manually specifying the columns just for these tables.
What is really needed would be something like SELECT * EXCEPT verylargecolumn FROM mytable ... So in most cases fetching to many columns would be no problem, but if there is really a column which is probelamtic you could easily request to ignore it.
They re correct in an "academic view" but most applications are CRUD and based on an ORM and there are not many columns fetched too much which will make any difference. There are some rare cases where these statements are correct, especially the lob fetching but in these cases most often the queries are switched to manually specifying the columns just for these tables.
What is really needed would be something like SELECT * EXCEPT verylargecolumn FROM mytable ... So in most cases fetching to many columns would be no problem, but if there is really a column which is probelamtic you could easily request to ignore it.
It's not jut about large columns.
When fetching from views, selecting unneeded columns can trigger unneeded JOINs.
Assuming no JOINed column is mentioned in the select list (either explicitly or through *), the query optimizer can eliminate the OUTER JOIN, or INNER JOIN when it is known that the referenced row exists (because there is a FOREIGN KEY).
This is mentioned in the article as "Oracle’s join elimination transformation", but (most?) other databases can do it too.
When fetching from views, selecting unneeded columns can trigger unneeded JOINs.
Assuming no JOINed column is mentioned in the select list (either explicitly or through *), the query optimizer can eliminate the OUTER JOIN, or INNER JOIN when it is known that the referenced row exists (because there is a FOREIGN KEY).
This is mentioned in the article as "Oracle’s join elimination transformation", but (most?) other databases can do it too.
Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other table match the predicate, so you’d still need to do the join, right?
I guess it depends on the join predicate. For a vanilla equi-join, no grouping is required.
Let's say we have the following two tables and a view that joins them:
Let's say we have the following two tables and a view that joins them:
CREATE TABLE PARENT (
PARENT_ID int PRIMARY KEY,
PARENT_NAME varchar(255)
);
CREATE TABLE CHILD (
CHILD_ID int PRIMARY KEY REFERENCES PARENT,
CHILD_NAME varchar(255)
);
CREATE VIEW PARENT_CHILD
AS
SELECT
*
FROM
PARENT
JOIN CHILD
ON PARENT_ID = CHILD_ID;
SELECT
*
FROM
PARENT_CHILD;
SELECT
PARENT_ID,
PARENT_NAME
FROM
PARENT_CHILD;
SELECT
CHILD_ID,
CHILD_NAME
FROM
PARENT_CHILD;What I’m suggesting is that the following still has to access CHILD despite only projecting columns from PARENT. If I understand it correctly, not projecting any columns from a joined table doesn’t mean it won’t be accessed, even for vanilla equi-joins.
SELECT
PARENT_ID
FROM
PARENT_CHILD;My experience is quite contrary :)
I prefer to not use ORM (even if I wouldn’t mind, clients often ask me to work with the DB directly, without ORM), and every selected column matters - in terms of performance and amount of transferred data. Some fields are TEXT/BLOB and sending thousands of them “just because” is not cool.
As other commenter mentioned the BigQuery supports the `SELECT * EXCEPT(column names...) FROM mytable` syntax. Also, it is worth noting the `SELECT COUNT(*) FROM mytable` in BigQuery is faster and cheaper than `SELECT COUNT(always_truish_column) FROM mytable`.
This performance optimization is valid for most databases:
COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)
COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)
I was told years ago that count(1) was faster than count(*) and have been using that blindly and without questioning since (MSSQL user), although I can't remember last time I submitted code that did that. Often write it when examining something in the database though.
These specific tricks do exist for every database. But there is no reason for mssql to not handle count(*) and count(1) the same, they are the same. Maybe they are already the same and it‘s just a very old trick which has been replaced by a better query optimizer.
In PostgreSQL count(1) is slower since PostgreSQL does not optimize count(constant) to count(*).
EDIT: PostgreSQL might be able to optimize that with LLVM but for short running queries it won't.
EDIT: PostgreSQL might be able to optimize that with LLVM but for short running queries it won't.
Bigquery is surprisingly dumb when it comes to rearranging your query to be able to execute it faster. It seems to entire focus of the bigquery team is being able to parallelize all the work to be done, rather than seeing how they could do less work in the first place. The former is clearly interesting when you bill per Gigabyte of data processed...
I suspect that stems from the fact it doesn't have the pregenerated statistics that other databases have, and therefore there isn't much scope to make a smart query optimizer.
I suspect that stems from the fact it doesn't have the pregenerated statistics that other databases have, and therefore there isn't much scope to make a smart query optimizer.
Just to be clear, BigQuery bills per byte scanned. So how much work they have to do on the data is almost irrelevant from a cost perspective.
There are improvements to decrease data scanned, for example, good sort orders and partitioning. BQ supports predicate pushdown for both of these.
There are improvements to decrease data scanned, for example, good sort orders and partitioning. BQ supports predicate pushdown for both of these.
[deleted]
The second part is due to that count(*) would actually be better written as count() if it was not for the weirdness of the SQL standard.
Count(*) makes sense as you can count(column), count(column1, column2, ...), Count(distinct column), and count(distinct column1, column2,...) Just like in a select.
I don't know of any database that supports multi column count. But even if one did, count(*) does not have the semantics that you would expect from that. If the semantics were equivalent to expanding the column list then count(*) with a single nullable column would return the number of non-null values. But instead it is defined to return the number of rows regardless of context, being equivalent to count() or count(1).
I'm not entirely sure why I thought I've done a count with multiple columns before because it does indeed seem not to be a thing.
[deleted]
[deleted]
This is supported by some databases and DWHs. I work with BigQuery and they do have that syntax
>most applications are CRUD and based on an ORM
Do you actually believe that most CRUD apps use an ORM?
Do you actually believe that most CRUD apps use an ORM?
Yes? Most web frameworks do include some form of ORM.
The existence of an ORM in your framework doesn’t mean you necessarily use it. I’ve worked on several projects where the ORM is acknowledged as very handy when you are doing a prototype/poc and for really simple things, but for anything even a little complex you should be writing your own SQL by hand so you know exactly what the db is doing and the cost of it. I personally have come to the conclusion that letting an ORM generate nontrivial SQL for you is actually harder than just writing the SQL yourself, because you have to think “how do I get this higher-level abstraction to generate the actual SQL I want?” and at that point you may as well just write SQL.
I mean, 90% of my application is simple things. I mostly use the Django ORM and even more complicated joins, grouping and subqueries are predictable. I'm curious what ORM people are using that obscures the queries that are being emitted so much. For the 10% where things are complicated I will happily write SQL.
I wouldn't say the ORM is "for prototypes/pocs", but rather it should be the default and raw SQL is "complicated stuff only".
I wouldn't say the ORM is "for prototypes/pocs", but rather it should be the default and raw SQL is "complicated stuff only".
I've become convinced that the best ORM approach is for the ORM to generate code from SQL and not the other way around.
Anecdotally yes, most small to medium sized CRUD apps do use either an ORM or a query builder so abstracted as to be effectively an ORM.
> I explicitly specify the columns of interest in the select-list (projection), [...] for application reliability reasons. For example, will your application’s data processing code suddenly break when a new column has been added or the column order has changed in a table?
Application reliability for me is the main reason. Better performance is just a plus.
Application reliability for me is the main reason. Better performance is just a plus.
It's the main reason for me too, but in an increasing number of modern contexts I think making SELECT * work well is part of application reliability. I've encountered quite a few users who add new columns all the time (or consume data from a dozen other groups who add new columns semi-frequently), so if we don't enable them to use SELECT * that just means they have to constantly rebuild and redeploy their data processing code.
In most cases it is better to let application fail as early as possible when something unexpected occured. By making it more stable that way (SELECT *), you create hidden bugs which hard to detect and recovery is more expensive. It is even more important for data processing pipelines. I add assert/except to my code when it is possible: fail job, analyze, fix/report bug, notify downstream, rerun.
Absolutely this was drummed into me 20+ years ago when I worked on my first major sql based project
But that argument only holds true of you rely on the column order. Most modern applications just specify column names where this doesn't matter.
A lot of best practices from 20 years ago are no longer valid. Like the excessive normalisation. These days data storage isn't always the major limiting factor anymore, in many cases it's better to have less tables with more data.
A lot of best practices from 20 years ago are no longer valid. Like the excessive normalisation. These days data storage isn't always the major limiting factor anymore, in many cases it's better to have less tables with more data.
[deleted]
Well if your specifying columns your not by definition doing a select *
I meant specifying columns during extraction of the rows.
Like using name=result_row("name") instead of name=result_row(1)
The former works even with SELECT * irrespective of the order of the columns. The latter won't.
Like using name=result_row("name") instead of name=result_row(1)
The former works even with SELECT * irrespective of the order of the columns. The latter won't.
I'm seeing a lot of comments on why SELECT * is fine or not, but it seems like the bigger issue is that (in general) SQL has two very limited ways of letting your select columns: explicitly naming each column, or getting all columns. This means that when you are concise, you sometimes end up getting columns you don't need.
For example, R's dbplyr library lets you write queries that select all columns that start_with("something_"). This is revolutionary, because now a person can use the semantics of column names in their selection!
Granted, dbplyr ultimately generates a query that explicitly names the columns, so it's not a within-SQL solution, but I've been surprised at how useful the behavior is!
For example, R's dbplyr library lets you write queries that select all columns that start_with("something_"). This is revolutionary, because now a person can use the semantics of column names in their selection!
Granted, dbplyr ultimately generates a query that explicitly names the columns, so it's not a within-SQL solution, but I've been surprised at how useful the behavior is!
[deleted]
SELECT * FROM table WHERE dt = CURRENT_DATE() LIMIT 100;
Probably the most common code run in my Datagrip instance. I run it so frequently that I created a parameterized version with a shortcut.
Probably the most common code run in my Datagrip instance. I run it so frequently that I created a parameterized version with a shortcut.
I was never a fan of SELECT * as it felt lazy, and a bit of a pain when joining multiple tables. Also, if you happen to only need the columns that are covered by indexes (a covered query), then you are actually really slowing yourself down quite a bit. Extra columns mean extra memory and extra bandwidth. They also affect caching.
Plus, despite everything, the optimizer is not perfect and some extra columns might change your query plan (Sybase had so much fun with this stuff).
Plus, despite everything, the optimizer is not perfect and some extra columns might change your query plan (Sybase had so much fun with this stuff).
I had a coworker who was just lazy and wanted to use * even if he only needed to get one column from a database with dozens of columns. We ended up having to ban the use of * all together.
Should be fine if you're selecting from an upstream volatile/temp table you've created. Makes the code a bit easier to read if you're doing nested select statements.
A title so broad as to be almost necessarily wrong
(118 comments as I write this and no one has used the word "profile"!?)
“The real problem is that programmers have spent far too much time worrying about efficiency in the wrong places and at the wrong times; premature optimization is the root of all evil (or at least most of it) in programming.”
~Knuth (or maybe Hoare)
https://xkcd.com/1691/
“The real problem is that programmers have spent far too much time worrying about efficiency in the wrong places and at the wrong times; premature optimization is the root of all evil (or at least most of it) in programming.”
~Knuth (or maybe Hoare)
https://xkcd.com/1691/
It selects all columns. Why/when wouldn't this be bad for performance is a more interesting question, no?
(to be clear, I still enjoyed reading this.)
(to be clear, I still enjoyed reading this.)
There are CSV-backed "databases" that you can query with SQL. Selecting a row and returning it is basically copying a string once and replacing all CSV-separators by record separators. Doing a projection requires you to copy the string, remove all fields that are not requested, reorder the fields as requested, resulting in a few more copies, and only then transmitting the answer.
So in this case it would be slower to not do 'select *'.
However, those things have always been exotic, and with the advent of SQLite have become even less common.
However, those things have always been exotic, and with the advent of SQLite have become even less common.
If you're using wildcards, you're gonna have a bad time.
And this is even more true when using columnar databases.
Of course, all of this goes completely out the window if your company mandates an ORM for database access, which i feel it’s pretty safe to say about 90% of companies do.
But that’s the point of ORMs. Disregard anything that makes your database different than any other, and any of its optimizations, so that you can pretend raw data fits an OO-paradigm and feel safe because you can go `customer.name = “dork”; customer.save();` and make anything more complicated than that Somebody Else’s Problem.
But that’s the point of ORMs. Disregard anything that makes your database different than any other, and any of its optimizations, so that you can pretend raw data fits an OO-paradigm and feel safe because you can go `customer.name = “dork”; customer.save();` and make anything more complicated than that Somebody Else’s Problem.
If you're suggesting that there's room for improvement in ORMs, I agree. It'd be nice if the ORM figured out exactly what was going to happen and just fetch the data exactly required. Maybe that's something to work towards.
If you're suggesting ORMs are bad because they make developer lives easier, I don't understand how one relates to the other.
If you're suggesting ORMs are bad because they make developer lives easier, I don't understand how one relates to the other.
I’m not saying developer-friendly APIs are bad, but i do think hand-crafted, well-written, and well-tested SQL outperforms any ORM, and if any ORM comes close to the performance of hand-crafted queries, it does so at the cost of complexity.
Raw database queries aren’t difficult except in extreme edge cases that most ORMs aren’t smart enough to handle either. ORMs do make things slightly “easier”, but that comes at a cost. Whether that cost is in terms of performance or complexity or developers losing understanding/knowledge of how to build code that leans into the benefits of whichever database you choose comes down to whichever ORM you’re using, but that trade off will always be there.
And either way, 99% of people using any random ORM have no idea whether a `select *` is being used or not. That’s the whole point. You put blind faith into whatever ORM believing it will do the “right”/“most optimized” thing.
Raw database queries aren’t difficult except in extreme edge cases that most ORMs aren’t smart enough to handle either. ORMs do make things slightly “easier”, but that comes at a cost. Whether that cost is in terms of performance or complexity or developers losing understanding/knowledge of how to build code that leans into the benefits of whichever database you choose comes down to whichever ORM you’re using, but that trade off will always be there.
And either way, 99% of people using any random ORM have no idea whether a `select *` is being used or not. That’s the whole point. You put blind faith into whatever ORM believing it will do the “right”/“most optimized” thing.
On top of that you only have to learn SQL once and you can use it pretty much everywhere without having to learn a different ORM - Learn once, write everywhere.
Another thing that speaks for raw SQL is that it's a lot easier to debug queries, just copy/paste the query into your SQL editor and start figuring out what's wrong, you can't just do that with an ORM.
Another thing that speaks for raw SQL is that it's a lot easier to debug queries, just copy/paste the query into your SQL editor and start figuring out what's wrong, you can't just do that with an ORM.
"debug queries"
yea, that's the point where things start getting exciting when you have logic in queries that you actually have to debug.
I too love 200 LoC (tiny, in fact) procedures that inside build ""dynamic SQL"" aka string concat and EXEC with many OUTPUT parameters
10/10 experience, would recommend it to everyone.
No, I don't want to debug my queries because it means that I'm probably doing too much on the database.
I treat database more like a fancy data storage with outdated language, not as a business logic layer.
yea, that's the point where things start getting exciting when you have logic in queries that you actually have to debug.
I too love 200 LoC (tiny, in fact) procedures that inside build ""dynamic SQL"" aka string concat and EXEC with many OUTPUT parameters
10/10 experience, would recommend it to everyone.
No, I don't want to debug my queries because it means that I'm probably doing too much on the database.
I treat database more like a fancy data storage with outdated language, not as a business logic layer.
There's a cost to everything, including enumerating just the fields you need from the query. When a new column is added, does a developer now need to revisit every query involving that table, update if necessary, and retest? That has a very real cost in terms of time and money, probably more than the cost of 'SELECT *', or not using an ORM.
A lot of times, the technically 'optimal' solution isn't necessarily optimal.
A lot of times, the technically 'optimal' solution isn't necessarily optimal.
Add it only in the queries that require that column. Knowing where you need a new column should make adding a field easy.
Most ORM's I've used carefully choose just the columns required. i.e. they don't use 'select *'.
In some cases its not that simple, for example with Hibernate if you select specific columns then the object you get back won't be put in its L1 cache because its not the full object.
So in some cases its better to select the whole object by primary key because some other method would do that later anyhow and now its in the cache and will skip an extra db call.
So in some cases its better to select the whole object by primary key because some other method would do that later anyhow and now its in the cache and will skip an extra db call.
Use a better ORM.
All ORM's are not equal. It is a broad class of frameworks designed for different purposes and with different trade-offs. My favorite is EF Core which allow you to select exactly the fields you want or do the equivalent to "select *". I'm sure there other ORM's which support the same.
All ORM's are not equal. It is a broad class of frameworks designed for different purposes and with different trade-offs. My favorite is EF Core which allow you to select exactly the fields you want or do the equivalent to "select *". I'm sure there other ORM's which support the same.
> Use a better ORM.
This so many times. I also think before people talk about an ORM they need to define ORM as they can range from something like Hibernate to a simple object mapper. Is something like jOOQ and ORM where I can write typed SQL and it maps results to objects?
I prefer writing SQL, but also prefer using a library that can do the tedious object mapping for me.
This so many times. I also think before people talk about an ORM they need to define ORM as they can range from something like Hibernate to a simple object mapper. Is something like jOOQ and ORM where I can write typed SQL and it maps results to objects?
I prefer writing SQL, but also prefer using a library that can do the tedious object mapping for me.
But that’s the point of ORMs
No, the point of ORMs is to map between relational databases and object-orientated programming environments, both of which are things which exist for good reasons.
Your analysis is shallow and ill-informed – particularly given that many ORMs will very carefully select the columns which are selected in any particular query.
No, the point of ORMs is to map between relational databases and object-orientated programming environments, both of which are things which exist for good reasons.
Your analysis is shallow and ill-informed – particularly given that many ORMs will very carefully select the columns which are selected in any particular query.
Knowing whether to `SELECT *` vs `SELECT a,b,c` is the entry-level/babys-first-optimization case. Providing a high level and performant ORM, takes deep knowledge of the underlying database and a lot of code complexity. But that wasn’t really my point. My point was:
If you’re using an ORM this whole article is moot to you. You don’t decide whether `SELECT *` is the being used or not (let alone more complex optimizations), and if you do actually delve this deep into your ORM you are in the vast minority of coders
if you’re using an ORM it’s an architectural decision that was mandated early on in whatever project, so even if you did find out a naive `select *` is being used by your third-party ORM, the whole point would be moot because we can’t just switch out ORMs for this project.
So for most people using an ORM, this is useless information because either you don’t know/care or your organization won’t let you know/care because they’re already doing it that way organization-wide.
If you’re using an ORM this whole article is moot to you. You don’t decide whether `SELECT *` is the being used or not (let alone more complex optimizations), and if you do actually delve this deep into your ORM you are in the vast minority of coders
if you’re using an ORM it’s an architectural decision that was mandated early on in whatever project, so even if you did find out a naive `select *` is being used by your third-party ORM, the whole point would be moot because we can’t just switch out ORMs for this project.
So for most people using an ORM, this is useless information because either you don’t know/care or your organization won’t let you know/care because they’re already doing it that way organization-wide.
Any sane ORM will allow you to specify what data you want, and only select that.
I'd argue that using an ORM as default and handcrafting optimized queries for specific edge cases should be the way to go.
I'd argue that using an ORM as default and handcrafting optimized queries for specific edge cases should be the way to go.
weird.
.NET ORMs don't do this (at least Entity Framework Core)
`db.Users.Select(x => new { x.Name, x.Age }).ToListAsync()`
will perform something like `SELECT Name, Age FROM db.Users`
but ofc if you tell it to load everything `db.Users.ToListAsync()`
then it will perform
`SELECT Name, Age, Salary, ... FROM db.Users`
but not *
.NET ORMs don't do this (at least Entity Framework Core)
`db.Users.Select(x => new { x.Name, x.Age }).ToListAsync()`
will perform something like `SELECT Name, Age FROM db.Users`
but ofc if you tell it to load everything `db.Users.ToListAsync()`
then it will perform
`SELECT Name, Age, Salary, ... FROM db.Users`
but not *
I was about to post the same thing. This works perfectly fine for EF Core, as long as you're aware that the mapping has to be directly in the Select like this so that it can be translated to SQL.
Exactly, you just have to check how's your query evaluation process doing if you want to do fancy stuff that SQL may struggle with / don't understand
That made me realize how much I missed .NET and my favorite tool LINQPad. Add Dapper next to your advanced ORM and you're basically unstoppable.
I do agree that combination of powerful ORM + light wrapper over raw SQL like Dapper makes stuff way easier (faster, less boilerplate-ish) while maintaining decent performance.
People tend to say that: EF Core for saving data in db (because it detects changes and shortens code really hard)
and Dapper for reads - writting manually good queries
People tend to say that: EF Core for saving data in db (because it detects changes and shortens code really hard)
and Dapper for reads - writting manually good queries
I feel like this is a weird title. An equivalently bad title would be "SELECTing fewer columns than you need is bad for correctness."
"SELECT *" should be just as performant as "SELECT A, B, C" when your object only has A, B and C as columns.
"SELECT *" should be just as performant as "SELECT A, B, C" when your object only has A, B and C as columns.
It's fine for now, but it makes assumptions about the future state of your database.
The arguments presented in the article may or may not be applicable to your particular application, but I think it's a solid principle that you shouldn't assume your database will retain its schema indefinitely. If you're relying on "SELECT *" to mean "SELECT A, B, C", you're in for a bad time.
The arguments presented in the article may or may not be applicable to your particular application, but I think it's a solid principle that you shouldn't assume your database will retain its schema indefinitely. If you're relying on "SELECT *" to mean "SELECT A, B, C", you're in for a bad time.
> but I think it's a solid principle that you shouldn't assume your database will retain its schema indefinitely.
this is not a hard thing to anticipate and yet my coworkers thought otherwise...
this is not a hard thing to anticipate and yet my coworkers thought otherwise...
Maybe you're relying on your ORM object to have all columns available, even when you add more, and you're in for a good time.
SELECT * is quicker to transmit as a query, contains less tokens to parse, requires no lookups to column metadata to map from stored data structure to result structure... I’m pretty sure there must be some circumstances where it outperforms an explicit SELECT a, b, c query.
Perhaps to begin with just having A,B,C columns are fine and your comment justifies.
However, it still stands true in a test of time when you will end up with a lot more columns.
However, it still stands true in a test of time when you will end up with a lot more columns.
And when you add columns you need to a table, you will also need to add them to your query.
But I didn’t say it’s a good idea to do SELECT * (I never use it outside manual queries), just that it has no performance implication just by virtue of using it.
But I didn’t say it’s a good idea to do SELECT * (I never use it outside manual queries), just that it has no performance implication just by virtue of using it.
That's a non-useful pedantic correction, if I ever saw one...
And will never have more columns
Basically select * loads the entire row at the DB end. This has no effect on disk load times when data is stored in pages but since this is result of query it might get cached, might lead to more cache eviction and adds transfer overhead. Bottom line query what you need. Article is worth reading for the experimental setup used.
Nope, SQL database can split large values over multiple pages so selecting and extra column can mean more dusk IO.
I see so multiple page loads are also possible. Is the threshold documented ? Say for example for postgres ?
While SELECT * has no place in a production code, it's the right thing to do during data exploration with ad hoc queries. Only after I get sufficient understanding of the data, I'll restrict the columns.
I often see junior developers assume what they need just by the name of the columns, and miss something.
Oh wait, I just did that a few days ago. It was embarrassing.
Using LIMIT N is a great habit to develop to prevent yourself from hogging resources too, during exploration.
Can’t we just argue that using the * wildcard is just a great feature when you are writing sql queries on test data. I personally use it a lot when writing joins to eventually get the data I want or to execute a aggregating function like count().
But I think it is interesting to know if writing down all your table column names also delivers faster queries in other DBMS systems.
In theory yes. When you are joining two tables that have the same column names you have to alias them anyways.
Also with select * you are passing on any change to the source list of columns to your output, which may create problems (say the columns are reordered or a column is created/deleted).
Never do SELECT * when counting rows, SELECT(UIDPK) primary key for example instead.
Why? SELECT count(*) does not actually mean fetching all fields. It actually means fetching zero fields (the SQL standard is a bit weird) while SELECT count(uidpk) means fetching one field, but some databases optimize that to SELECT count(*) if uidpk is NOT NULL.
If network latency is a significant factor, and server storage and processing resources are sufficient, then the author's example of an 800 column table needs redesign.
Break that monster into smaller, more manageable tables and architect your database such that you can obtain exactly the data you need with well indexed joins, cached lookups, etc.
Break that monster into smaller, more manageable tables and architect your database such that you can obtain exactly the data you need with well indexed joins, cached lookups, etc.
800 columns in an OLTP is insane, that was my reaction when I read the OP. But in analytics it's not so unusual, although at that point a SQL DB is probably not the right solution anyway, and the data scientists probably do need all the columns.
> This is the most obvious effect - if you’re returning 800 columns instead of 8 columns from every row, you could end up sending 100x more bytes over the network for every query execution.
In theory, this makes sense, but 800 column is already another problem for your application/system. What if you need to select 196 of those columns, what are you going to do? What kind of SQL or Java/Python/NAME_HERE_OTHER_LANG code are you going to write to select those fields?
In theory, this makes sense, but 800 column is already another problem for your application/system. What if you need to select 196 of those columns, what are you going to do? What kind of SQL or Java/Python/NAME_HERE_OTHER_LANG code are you going to write to select those fields?
I would write a view and then you can do select * or move 800 columns names into rows in another table and create another table with an id,field and data column or separate...
[deleted]
Let's use this article so that we all understand that querying more than you need has memory implications and close any future discussions on this as it's already general knowledge.
* is the most evil and useful thing ever created for query
plus, if you do LIKE* the performance will go nuts
plus, if you do LIKE* the performance will go nuts
Isn't this something obvious?
There's use for the wild card, for instance if you need ad hoc query of data during development or testing.
If your table is not properly indexed however, will cause unnecessary select all query even if you didn't intend to.
Junior developer is bad for SQL performance but hey, everyone starts there, so there's nothing to be embarrassed about. Just code on!
There's use for the wild card, for instance if you need ad hoc query of data during development or testing.
If your table is not properly indexed however, will cause unnecessary select all query even if you didn't intend to.
Junior developer is bad for SQL performance but hey, everyone starts there, so there's nothing to be embarrassed about. Just code on!
[deleted]
So I think the case for selecting all of the columns (and for an ORM-like approach of selecting related fields at once) is to reduce the number of queries. Generally I think that selecting two columns at one point in application logic and firing off a separate query for another two columns later in execution of a script is going to be worse than selecting 6 columns. Query performance is not the only consideration here, you also need to consider network performance of transmitting the data from the database server to the client (although that equation might change if you're using an in-memory database or something like that).
Also, selecting all the columns at once has an ergonomics effect of making it simpler to take advantage of caching when writing multiple components which might query the same data within the same execution.
That being said, I do think selecting less columns is better than more, an even when using an ORM I find myself writing projects and helper objects to reduce how many columns I need in my queries, but I view that as more of an optimization, a secondary concern that I can take care of after the main logic is implemented, rather than a primary concern that should shape the initial implementation of the logic.
Of course, the performance concerns I have had might be vastly different than the original poster's, so take my comment with a grain of salt.
Also, selecting all the columns at once has an ergonomics effect of making it simpler to take advantage of caching when writing multiple components which might query the same data within the same execution.
That being said, I do think selecting less columns is better than more, an even when using an ORM I find myself writing projects and helper objects to reduce how many columns I need in my queries, but I view that as more of an optimization, a secondary concern that I can take care of after the main logic is implemented, rather than a primary concern that should shape the initial implementation of the logic.
Of course, the performance concerns I have had might be vastly different than the original poster's, so take my comment with a grain of salt.
This seems more useful if re-framed as "Why pulling data you won't use is bad for performance."
SELECT * seems like a strawman to me. I don't often see it in the wild anymore. But pulling more columns than you need is extremely common; it happens in every codebase I've ever seen. I habitually do it myself. More-or-less every time I choose to re-use a single function for retrieving data in several places. Because then the function needs to get the union of all the columns that all the callers need.
Which may be a reasonable trade-off. There's almost always a need to strike a balance between maintainability and performance. But it's also nice to have occasional reminders to re-assess what you've been doing. This particular practice tends to have a particularly high cost, and one that, depending on how you configure your environments, may be much larger in production than it is in development or CI.
SELECT * seems like a strawman to me. I don't often see it in the wild anymore. But pulling more columns than you need is extremely common; it happens in every codebase I've ever seen. I habitually do it myself. More-or-less every time I choose to re-use a single function for retrieving data in several places. Because then the function needs to get the union of all the columns that all the callers need.
Which may be a reasonable trade-off. There's almost always a need to strike a balance between maintainability and performance. But it's also nice to have occasional reminders to re-assess what you've been doing. This particular practice tends to have a particularly high cost, and one that, depending on how you configure your environments, may be much larger in production than it is in development or CI.
It can be extended upon.. I see a lot of ORM based solutions that pull a full record just to check a single property sometimes. E.g.:
var user = repository.GetUserById(userId);
return user.IsDisabled;
This is not necessarally terrible in some scenarios: namely, when the repository caches enties for the duration of the unit of work, and the user in question is used elsewhere in the request (or at least is used elsewhere in the common case).
In that scenario, this can be strictly more efficient than doing a specialized query here, and a more general fetch of the user later, because it becomes just one sql command instead of two.
Obviously though there are ORMs that don't offer such caching, or cases where the value will not be used again elsewhere in the request, and in those cases this is clearly undesirable. It is generally quicker and easier to do this than adding a new custom method to the repository to get exactly the desired data which is why it remains common even in those scenarios.
In that scenario, this can be strictly more efficient than doing a specialized query here, and a more general fetch of the user later, because it becomes just one sql command instead of two.
Obviously though there are ORMs that don't offer such caching, or cases where the value will not be used again elsewhere in the request, and in those cases this is clearly undesirable. It is generally quicker and easier to do this than adding a new custom method to the repository to get exactly the desired data which is why it remains common even in those scenarios.
I see that, too. But I don't think it's fair to blame on ORM. I also see it happening in non-ORM code that uses the repository pattern, for example.
For example, there's nothing about the code example you give that strictly implies the use of an ORM, just the use of some sort of layered design.
For example, there's nothing about the code example you give that strictly implies the use of an ORM, just the use of some sort of layered design.
I didn't meant to blame ORM, only highlight the (mis)use of them in this type of scenario - but yes, you are correct. It is an abuse of the repository - ORM or other. :)
Laravel Eloquent permits sparse loading of a model; but the way ORM is used in general, this might be a bug factory. Nevertheless I do use the technique where the objects exist only within the scope of a given subroutine.
On the other hand, if the user object was already recently used somewhere else it will be cached and this code will produce no database/network traffic at all.
The title of the post should have been phrased for the audience of ORM users. With ActiveRecord and such `SELECT *` is the norm rather than the exception. With an ORM it's even worse as its even allocating/converting data for the fields. Learn to use `pluck` or you ORM's equivalent.
Specifying the columns you want also allows you to create indexes that have the data you need as part of the index.
Select “a” from “b” where “c” = 1;
With a composite index on c and a allows you to fetch everything you need without even looking at the table.
Select “a” from “b” where “c” = 1;
With a composite index on c and a allows you to fetch everything you need without even looking at the table.
And this is SO important. Even if this is the sole reason why * should be considered bad.
In some cases on big databases, adding covering index is the way to make your apication perform well, by eliminating those table lookups.
In some cases on big databases, adding covering index is the way to make your apication perform well, by eliminating those table lookups.
Of course one of the few facilities for code-reuse in a platform that is pathologically difficult to keep DRY is, of course, Considered Harmful.
There are a lot of reasons NOT to use SELECT * but sending extra data over the wire is not one of them. This is just optimizing for a 2nd (or 3rd or lower) problem when there are much bigger and easier gains to be captured.
If wire-level payload size IS your big concern you should probably be looking at an entire myriad of things beyond your projection, like the actual query, or your protocol or caching or if you should even be using a RDBMS.
Yes, you should be more explicit in your queries, in which case
SELECT A,B,C FROM TBL;
should likely become
SELECT t.[A] ,t.[B] ,t.[C] FROM TBL t;
YMMV
If wire-level payload size IS your big concern you should probably be looking at an entire myriad of things beyond your projection, like the actual query, or your protocol or caching or if you should even be using a RDBMS.
Yes, you should be more explicit in your queries, in which case
SELECT A,B,C FROM TBL;
should likely become
SELECT t.[A] ,t.[B] ,t.[C] FROM TBL t;
YMMV
tl;dr
If you have indexes on column A but not B selecting * (a+b) will also need to fetch the row, otherwise it can fetch only the index.
The end.
To be fair, I've been using sql and optimizing it for a decade. The number of times "select *" was the offender was a minority. Typically N+1 is the biggest performance issue in modern frameworks and newer developers. Following that is complex logic that can be simplified to a more efficient sql.
select (star) is so far down the causes of slowdowns it is hardly worth mentioning 95% of the time.
0% of the time was db->server bandwidth the issue. Otherwise you're probably fetching a gig of data instead of filtering it in sql or paging.
If you have indexes on column A but not B selecting * (a+b) will also need to fetch the row, otherwise it can fetch only the index.
The end.
To be fair, I've been using sql and optimizing it for a decade. The number of times "select *" was the offender was a minority. Typically N+1 is the biggest performance issue in modern frameworks and newer developers. Following that is complex logic that can be simplified to a more efficient sql.
select (star) is so far down the causes of slowdowns it is hardly worth mentioning 95% of the time.
0% of the time was db->server bandwidth the issue. Otherwise you're probably fetching a gig of data instead of filtering it in sql or paging.
I've always wondered why SQL didn't have something like an EXCLUDE clause to make this easier for tables with lots of columns where you wanted a large number of them in the result. Something like (table with columns a through z):
SELECT * EXCLUDE g, m, p, x FROM table
SELECT * EXCLUDE g, m, p, x FROM table
Title is a bit click-bait-ish; I was expecting some deep insight of why this:
was superior to this:
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.