CODEWITHSUNDEEP
sql
AticusFinch
AticusFinch

Reputation: 2421

Easy SQL query performance question

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)?

Upvotes: 1

Views: 158

Answers (5)

Kristen
Kristen

Reputation: 4291

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 :)

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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.

Upvotes: -2

Wael Dalloul
Wael Dalloul

Reputation: 23024

Selecting the fields you need is faster, because it's bring less data. this is a general advice to increase the query performance speed.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166406

Selecting only what you need is faster, that goes for columns and where clauses.

Upvotes: 3

Gary McGill
Gary McGill

Reputation: 27526

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).

Upvotes: 7

Related Questions