23 ms·
Why SELECT * is bad for SQL performance (2020)
- giantDinosaur 6y agoIt 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.)
- corty 6y agoThere 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.
- nicetryguy 6y agoIf you're using wildcards, you're gonna have a bad time.
- orangepanda 6y ago> 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.
- Mauricebranagh 6y agoAbsolutely this was drummed into me 20+ years ago when I worked on my first major sql based project
- GekkePrutser 6y agoBut 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.
- deleted 6y ago[deleted]
- Mauricebranagh 6y agoWell if your specifying columns your not by definition doing a select *
- GekkePrutser 6y agoI 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.
- SpicyLemonZest 6y agoIt'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.
- nooorofe 6y agoIn 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.
- nivenkos 6y agoAnd this is even more true when using columnar databases.
- digiwano 6y agoOf 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.
- egwor 6y agoMost ORM's I've used carefully choose just the columns required. i.e. they don't use 'select *'.
- jeppz 6y agoIn 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.
- tester34 6y agoweird. .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 *
- Semaphor 6y agoI 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.
- wyattpeak 6y agoIt'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.
- lemagedurage 6y agoMaybe 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.
- ellisv 6y ago> 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...
- kaushikt 6y agoPerhaps 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.
- Semaphor 6y agoAnd 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.
- thecleaner 6y agoBasically 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.
- jeltz 6y agoNope, SQL database can split large values over multiple pages so selecting and extra column can mean more dusk IO.
- thecleaner 6y agoI see so multiple page loads are also possible. Is the threshold documented ? Say for example for postgres ?
- anarazel 6y agohttps://www.postgresql.org/docs/devel/storage-toast.html https://www.postgresql.org/docs/devel/storage-toast.html
- tpetry 6y agoThese 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.
- teraku 6y agoThis is supported by some databases and DWHs. I work with BigQuery and they do have that syntax
- pqb 6y agoAs 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`.
- tpetry 6y agoThis 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 ;)
- Zobat 6y agoI 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.
- jerzyt 6y agoWhile 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.
- ttz 6y agoUsing LIMIT N is a great habit to develop to prevent yourself from hogging resources too, during exploration.
- dutchmartin 6y agoCan’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.
- vinger 6y agoIn theory yes. When you are joining two tables that have the same column names you have to alias them anyways.
- cm2187 6y agoAlso 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).
- 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.
- funkisjazz 6y agoGetting more data than you need will result in getting more data than you need !? I for one am amazed.
- acd 6y agoNever do SELECT * when counting rows, SELECT(UIDPK) primary key for example instead.
- jeltz 6y agoWhy? 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.
- ttraub 6y agoIf 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.
- jerzyt 6y ago800 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.
- yilugurlu 6y ago> 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?
- deleted 6y ago[deleted]
- vinger 6y agoI 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...
- gandutraveler 6y agoLet'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.
- FirstLvR 6y ago* is the most evil and useful thing ever created for query plus, if you do LIKE* the performance will go nuts
- damowangcy 6y agoIsn'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!
- deleted 6y ago[deleted]
- InfiniteRand 6y agoSo 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.
- mumblemumble 6y agoThis 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.
- Jestar342 6y agoIt 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; That's not even a facetious example, I have seen it multiple times. In some cases that query is pulling multiple columns, and a few joins.. just to pull a single bit value.
- mumblemumble 6y agoI 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.
- Jestar342 6y agoI 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. :)
- karmakaze 6y agoThe 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.
- rubyist5eva 6y agoSpecifying 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.
- jve 6y agoAnd 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.
- Pxtl 6y agoOf course one of the few facilities for code-reuse in a platform that is pathologically difficult to keep DRY is, of course, Considered Harmful.
- skeeter2020 6y agoThere 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
- Justsignedup 6y agotl;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.
- closed 6y agoI'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!
- deleted 6y ago[deleted]
- edroche 6y agoI'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
- carapace 6y ago(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/ https://xkcd.com/1691/
- markus_zhang 6y agoSELECT * 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.
- protomyth 6y agoI 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).
- irrational 6y agoI 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.
- scottmcdot 6y agoShould 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.
- spacemanmatt 6y agoA title so broad as to be almost necessarily wrong