Is there a performance gain achieved by using "SELECT Field1, Field2, Field3 ..." instead of "SELECT * ..."

Question

Is there a performance gain achieved by using:

SELECT Field1, Field2, Field3 ... 

instead of:

SELECT * ...

Answer 1

Yes, there is. It will mostly be in the IO and network traffic savings coming from selecting fewer columns. This can be quite significant, as most of the time spent in a client comes from the network and disk IO on the database server. This of course assumes you are selecting less columns then there are in the table.

As a rule, you should only select fields that you are going to use.

This is good practice as you are also making sure that your query is not affected by future changes to the table (addition of columns).

Answer 2

If the list field1, ..... represents a sub set of the available columns then the answer is yes. How much more efficient depends on how much smaller the subset is.

For each row processed there are a number of "per column" operations depending on the column type this can be as little as re-alinging bytes to an integer boundry or as heavy as allocating suitable chunck of memory and reading in a large BLOB from another file.

However most seasoned programmers will always code a specificic column list even if there are little or no performance benefits. There are a couple of reasons the most important being that it helps insulate your program from changes in the datatbase schema -- your program will not blow up in someone adds or reorders columns, secondly is readability you dont need to keep going back to the DB schema to see what your program is getting from the table, and thirdly it helps with impact analysis as you can scan your source code for usage of particular columns.

Answer 3

Another performance issue is if you do this in views. If you put select * in views, this will affect the indexing that can be done to the views, forcing them to re-scan the table to find the list of columns to select.

Here's a q/a that also mentions this http://bytes.com/topic/sql-server/answers/480715-select-views

Answer 4

My personal experience with this is that any performance difference is not visible to the naked eye. But you should still specify the columns you want rather than * (except in things like COUNT(*)) because you are less likely to have the application fail in unexpected ways if the definition of the table changes.