When designing an SQL query, which is faster, selecting the whole row (select *) or only the fields you need? What if the results were fed to another query (eg a natural join)?
+7
A:
Selecting only the fields you need will generally be faster - especially if those fields are "covered" by an index, so that the database need only load the data from the index and not from the main table (thus allowing it to fetch far fewer pages).
Gary McGill
2009-11-12 09:15:36
+3
A:
Selecting only what you need is faster, that goes for columns and where clauses.
astander
2009-11-12 09:15:59
I'd be cautious with general statements about WHERE clauses. There could be edge cases where having the WHERE clause leads to a very weird access path, that might be slower than reading the whole table. I do not think this can ever happen with restricting columns.
Thilo
2009-11-12 09:21:02
Aggreed. But in general, limiting the data returned is a better approach.
astander
2009-11-12 09:33:53
A:
Selecting the fields you need is faster, because it's bring less data. this is a general advice to increase the query performance speed.
Wael Dalloul
2009-11-12 09:17:13
A:
Selecting all columns can be faster if you access the table several times and the overhead of having more hard parses is bigger than the overhead of selecting more columns than necessary.
E.g. lets say you have a table foobar with columns a..c, all of them CHAR(1); then the following sequence of statements
SELECT a,b,c FROM foobar;
SELECT a,b FROM foobar;
SELECT a,c FROM foobar;
SELECT a FROM foobar;
SELECT b,c FROM foobar;
SELECT b FROM foobar;
SELECT c FROM foobar;
might be slower than executing
SELECT * FROM foobar;
seven times.
ammoQ
2009-11-12 09:25:29
Yes, but the seven different versions will also be cached and once you "SELECT c FROM foobar" for the second time it will be a soft-parse, too.
Thilo
2009-11-12 09:46:40
Thilo: sure. In general, I would measure performance before optimizing anything.
ammoQ
2009-11-12 11:13:36
gbn: what works well and what doesn't is not determined by a majority decision
ammoQ
2009-11-12 19:54:49
If selecting all columns (I hope you don't think that explicitely listing all columns is better in any way than select *) is that bad, why does anyone use an ORM? Does hibernate etc. know which columns you really need? Nope. In many cases, your class contains a field for every column in the table, and whenver you use the ORM to retrieve that object, a "select *" - or the equivalent "select col1, col2, ..., coln" happens.
ammoQ
2009-11-12 21:02:33
Another example: Oracle's PL/SQL allows you to define a record like that: mytable%rowtype. The _only_ proper way to fill such a record in a select is a "select *"
ammoQ
2009-11-12 21:08:49
+1
A:
SELECT * is particularly dangerous when you add columns in the future. Lets say you have a routine that needs the UserID and LastLogon date. Last you add a UserNotes TEXT column to the table and Bang! that little routine will now be pulling some (potentailly vaste) text data too, performance of the whole application will deteriorate and EVERY Select statement in the whole application will need to be fixed to cure the problem!
Just select the columns the application needs :)
Kristen
2009-11-12 10:02:48