Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT * performance

Posted on 2009-07-03
11
Medium Priority
?
506 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:Jaime Olivares
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24775359
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24775364
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24775368
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 12

Assisted Solution

by:kevin_u
kevin_u earned 200 total points
ID: 24775369
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
 
LVL 55

Author Comment

by:Jaime Olivares
ID: 24775382
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 400 total points
ID: 24775389
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
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1400 total points
ID: 24775390
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
 
LVL 55

Author Closing Comment

by:Jaime Olivares
ID: 31599703
Thanks all for your comments
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24775419
jaime, one more suggestion. You can prove the queries yourself by analyzing the execution plan for each query. That is always my approach.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24775448
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
 
LVL 55

Author Comment

by:Jaime Olivares
ID: 24775809
thanks for the extra info
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question