Performance issue in using SELECT *?

Question

Possible Duplicate:
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
What is the reason not to use select *?

Is there any performance issue in using SELECT * rather than SELECT FiledName, FiledName2 ... ?

Solution 1

If you need a subset of the columns, you are giving bad help to the optimizer (cannot choose for index, or cannot go only to index, ...)

Some database can choose to retrieve data from indexes only. That thing is very very helpfull and give an incredible speedup. Running SELECT * queries does not allow this trick.

Anyway, from the point of view of application is not a good practice.

---

Example on this:

• You have a table T with 20 columns (C1, C2, ..., C19 C20).
• You have an index on T for (C1,C2)
• You make SELECT C1, C2 FROM T WHERE C1=123
• The optimizer have all the information on index, does not need to go to the table Data

Instead if you SELECT * FROM T WHERE C1=123, the optimizer needs to get all the columns data, then the index on (C1,C2) cannot be used.

In joins for multiple tables is a lot helpful.

Solution 2

Take a look at this post:

What is the reason not to use select *?

and these:

• Performance benefit when SQL query is limited vs calling entire row
• Which is faster or best, select * or select column1 colum2 column3
• SQL query question select * from view or select col1 col2 col3 from view
• Which is faster, select * or select column1 column2 etc

Solution 3

Every time you do a select *, there is may be an additional query to get the list of columns. In high transaction environments this could become a visible overhead, but once every now and then will make no difference.

Also, when inserting records, never use select * in an insert in case columns are added.

Solution 4

The only performance issue will be if your application only needs a subset of the fields returned by select *. There is no performance difference in the database as they are effectively the same thing.

Solution 5

I don't know about computing performance but in terms of read/maintain-ability (i.e. Human Performance) we don't use select * at my shop. Everything is explicitly selected.

View more solutions

Comments

[StingyJack]

There is no query plan difference between the two, but if the table gains an extra column then the query does not execute as it originally did and it returns more data than is probably needed.

[Adam Bellaire]

The DB has to "find each column" whether you name them explicitly or use *. In the case where you want all columns, the performance is identical. However, disallowing * forces you to think about what you really need from the DB, which is a good practice.

[splattne]

Nice. I guess to in order to gete a "fair" result, you should include more that one column in the second select statement.

[jmucchiello]

He should, in fact, name all of the fields on the table to make a fair test.

[coopejoh]

i just wanted to ask you - when you specify a field in a table explicitly in select, server checks if the field really exists, so there is still additional query or am i wrong?

[Pop Catalin]

This is not entirely accurate (well for some databases at least), most top tier databases prepare a plan for a query an cache it, so whether you use * or col list the list of columns is still queried at plan compile time. The query cache is invalidated when DDL changes occur on the table.

[Juliet]

I thought indexes were only relevant in JOIN, WHERE, and GROUP BY clauses. Someone can correct me if I'm wrong, but how do columns in the select clause prevent the optimizer from selecting an index?

[Andrew Hare]

That post has more to do with maintainability than performance. I agree with the answer to that post that select * is an anti-pattern but this question was about performance and whether or not there is a difference.

[Omid]

@Princess I've updated the post with an example

[Diomidis Spinellis]

With all fields specified there should be no perceptible performance difference (the overhead is simply getting the names of the table's columns).

[Yup]

I accept this , but I think other reasons "not to use it " counter this example use of SELECT *.

[Fenton]

+1 - This is oft-overlooked in answering this question. SELECT col1, col2, col3 and SELECT * are the same if there are only three columns named col1, col2 and col3.