Select * or select only the columns you need

Question

Select * or select only the columns you need

Søren

Mr or Mrs. 500

Points: 548
More actions
June 23, 2008 at 1:34 am

#381288

I have a customer that has an strange ERP-system. If they ask for warehouseinformations for 1 product, it generates 1000 Queries.
That should be reduced, but I am beginning to be interested in one issue: What is the performance gain/loss by specifying Select *
instead of only selecting the columns that you need.
My first thought is that by Selecting only the columns you need, you would reduce networktraffic, but what are your opinions.
Best regards
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

Even if you select form a single table, select * would need all the columns returned, so SQL Server will need to scan through the whole table. If you need only one particular column, it may be sufficient to traverse one of the indices, which may mean that SQL Server reads in only a fraction of the pages from disk -> much faster response. If you have more tables involved, looking up the data for each row from each table could be saved. ...

And, as you mention there is the network traffic, application caches, ...

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 2

instead of using select * from table1

select primay keys of the table as primary keys will have a default clustered index. This will reduce the the number of physical reads and logical reads.

Srikanth Sv

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

henri flokstra SSC Eights! Points: 939 More actions · Answer 3

By default queries are performing better only to select the columns you need, and put the appropiate indexes on them. If you want to learn more about indexes, you can dig into this article

(of course it depends on the amount of records your are retrieving and how often)

------------------------------------------------------------
Application Architect and Developer @ Traffic4u
Visit my profile on LinkedIN

Perry Whittle SSC Guru Points: 233792 More actions · Answer 4

AFAIK the only time its really valid to use a select * is inside a correlated query, dont know if anybody else can add to or confirm this

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

einman33 SSCrazy Points: 2813 More actions · Answer 5

Issues can also occur when * is used and new attributes are added to the entity. Best case it only adds confusion in the middle tier, worst case data gets swamped around and chaos insues. Performance is better if you only consume what you need.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

A good way to validate the fact that the select * is going to be more costly is to look at the execution plan for the query and by using SET STATISTICS IO option to see what your IO costs are in both scenarios. Chances are you will see a marked difference. Example;

SET STATISTICS IO ON

SELECT *

FROM MY_TABLE

WHERE COL_A = 'XXXX'

SET STATISTICS IO OFF

Do that with the defined column list as well and check out the reads associated with the query. Also check out the execution plan as stated earlier (hit Control L to display this) and see what indexes are being used in both scenarios. If there is a good covering index for all the columns associated to the query you should find some improved subtree costs overall.

Have fun! Analyzing query performance is one of the best ways to learn what is going on with SS under the hood. It will certainly keep you guessing and challenged.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot