SELECT * performance

Hi all,
I want to know if there is an advantage of using SELECT field1, field2, .... agains SELECT * in terms of performance.
Please forget other considerations. Just want to know about performance.
I will award all interesting comments.
Thanks in advance,
Jaime.
LVL 55
Jaime OlivaresSoftware ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
if you are giving * instead of field names which are required than you are selecting those columns also which you are not going to use and it will take time to load so better to have field names as long as possible.
0
RiteshShahCommented:
try to specify only the columns you'll need. This will:

Reduce memory consumption and network bandwidth
Ease security design
Gives the query optimizer a chance to read all the needed columns from the indexes
0
RiteshShahCommented:
in short, you will have less IO and less network traffic if you specify column after select statement and hence you will get good performance.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kevin_uCommented:
I'd say the primary consideration is that select field1, field2, etc has the advantage of not transfering as much data from the server to the client.  This may be considerable if the client and server are separated by a wan link.

For tables with many columns, its possible it may save some disk read bytes.

The compile of the statement may have a tiny tiny effect, negligible.

The result set may be more human readable.. if that applies.

The execution optimizer may be able to skip some steps and choose a better solution for non-indexed table joins.

Thats what I can think of for now.
0
Jaime OlivaresSoftware ArchitectAuthor Commented:
Hi RiteshShah and kevin,
I want to evaluate only the server's query process itself, not post-process in client machine.
Please rephrase just considering this. Also, I will require all fields anyway.
0
RiteshShahCommented:
well, if you required all field than I guess you will not have any performance benefit, however it will not be human readable clearly but that's fine, no performance benefit
0
mrjoltcolaCommented:
Since you requested we forget all other considerations, I will not address why select * is a bad practice in most cases.

From the database execution perspective, no, there is not a performance advantage in the general case, assuming you are comparing selecting every column explicitly vs select *.

The amount of IOs and execution time will be identical (in the general case).

Where the individual fields wil perform better are:

1) If some columns are in an index and some are not. If you query only fields in an index, the DB engine may scan only the index rather than the table segment. This is more efficient.

2) Querying a subset of the columns will reduce the data volume returned in the cursor, and over the network.


As far as the parsing overhead or the DBMS engine execution overhead, they are identical. Actually it may be simpler for the engine to select * since there is no column filter to process.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jaime OlivaresSoftware ArchitectAuthor Commented:
Thanks all for your comments
0
mrjoltcolaCommented:
jaime, one more suggestion. You can prove the queries yourself by analyzing the execution plan for each query. That is always my approach.
0
Aneesh RetnakaranDatabase AdministratorCommented:
i know this is already closed, one cent from myside

if you use the column names, sql server has to do an addition check on the system tables to see whether there is a column by that name
0
Jaime OlivaresSoftware ArchitectAuthor Commented:
thanks for the extra info
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.