Efficiency of "SELECT *" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Efficiency of "SELECT *"

Author

Topic

chris_t
Starting Member

6 Posts

Posted - 2005-02-03 : 11:33:36

This has probably been asked before, but its a nightmare to search for.

If you genuinely need to select all columns from a table is it any less (or more) efficient to use "SELECT *" rather than "SELECT column1, column 2 etc"

I have been told it is less efficient as SQL Server has to look up the column names. I was under the impression it was only less effiecient if you end up selecting something like a text field that you don't require.

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-03 : 11:43:59

Less efficient for the reason you mention. It has to look up the column names rather than you giving the to it.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-02-03 : 11:46:03

http://www.adopenstatic.com/faq/selectstarisbad.asp

some more reasons but not sure?

chris_t
Starting Member

6 Posts

Posted - 2005-02-03 : 12:13:53

Has anyone an idea of how much of an overhead looking up the columns names puts onto executing the SQL - is it significant enough to make a noticable difference under load?

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-03 : 12:58:26

I don't have any numbers, but I doubt it is extremely significant. However, even if there were no performance hit, I would suggest listing out the columns. Here is an excerpt from the above link that supports my feeling on Select *

quote:

SELECT * is lazy coding practise. It's probably best to start with good habits early. As well the old saying of "a stitch in time saves nine" is very true. Imagine having to trawl through a 1000 line ASP page that you coded 6 months ago trying to find all the fields you used to edit a SELECT statement at the top of the page. Then imagine doing this for 1000 pages! Better to do it right the first time.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-03 : 13:15:27

Yet another reason to use explicit column lists is that say you have an old SP that inserts records into a #temp table (not select into)
ie:
create table #temp (col1, col2)
Insert #temp(col1, col2)
Select * from RealTable

later on when someone decides to add column col3 to the RealTable the SP breaks.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-03 : 14:04:27

Have a look

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-03 : 16:32:30

Since were on the subject of Select statements:
Which do you guys find preferable for finding missing records:

SELECT n.*
FROM Login L Right OUTER JOIN
Load_OUTPUT_New_User n ON L.username = n.username
WHERE (L.username IS NULL)

OR

Select * from Load_OUTPUT_New_User
Where Exists (
SELECT *
FROM Login)

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 17:18:49

I like WHERE EXISTS

Semper fi,

Xerxes, USMC (Ret)

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-03 : 19:24:24

I like left outer joins over right joins

SELECT n.*
FROM Load_OUTPUT_New_User n
LEFT OUTER JOIN
Login L ON n.username = L.username
WHERE (L.username IS NULL)

The other options are WHERE NOT EXISTS or WHERE NOT IN
( I have found WHERE NOT IN to be very slow.)
( WHERE NOT EXISTS is just as fast as LEFT JOIN but I am used to left JOINS)

Tim S

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-03 : 19:27:31

NEVER use a RIGHT OUTER JOIN. Never. $100 to the first person who can show me a good reason to us one.

I prefer LEFT OUTER JOIN, especially over the Exists() as you've coded it since it will not work (there is no relation between the WHERE clause and the table you are selecting from). Also, if you table has more than 1 column in its PK, LEFT OUTER JOIN is definitely the best (and perhaps only) way to do it.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-04 : 09:13:17

quote:
Originally posted by jsmith8858

$100 to the first person who can show me a good reason to us one.

Does being lazy count?

Brett

8-)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-04 : 09:25:19

Going back to the original topic

I have access to a single view on another server. The guy who wrote this used SELECT * for convenience (aka laziness). What he didn't know was that a week or so later the underlying table was changes which meant that the view stopped working properly. Pretty disastrous when you're trying to import the data from that view into a production system.

I've now re-written the view and passed it on so he can re-create it. I did suggest he may prefer a sproc but he wasn't interested

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-04 : 09:44:03

the continuation of "The Elwoos Saga", huh steve?

Go with the flow & have fun! Else fight the flow

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-02-04 : 10:01:20

I actually found a few instances of Select * in both of the 2 front end programs I bought off the shelf.

Of course I found them because I added a field to the table and the hole thing went to #&!!.

Never,Never,Never,Never,Never,Never,Never,Never
Use Select * in anything but a test.

Jim
Users <> Logic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-04 : 10:02:22

and in
where exists (select * from ...)

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-04 : 10:08:25

be especially carefully of using SELECT * in something that is compiled, like a VIEW or a stored proc.

look at the results of this carefully, it is kind of interesting:

create table Tmp(c1 int, c2 int, c3 int)
insert into tmp
select 1,2,3
go

create view TmpView
as
select * from Tmp

go

select * from TmpView

go

alter table Tmp Add c4 int

go

select * from TmpView

go

alter table Tmp drop column c2

go

select * from TmpView

go

drop view TmpView
drop Table Tmp

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-04 : 10:25:21

After Jeff's example I've sworn off SELECT * for good!

Semper fi,

Xerxes, USMC (Ret)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-04 : 12:58:08

quote:

NEVER use a RIGHT OUTER JOIN. Never. $100 to the first person who can show me a good reason to us one.

So is this your preference, or do you have some insight on LEFT OUTER versus RIGHT OUTER

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-04 : 14:09:22

RIGHT OUTER JOINs are not necessary, and using them makes no logical sense.

SELECT ...
FROM TABLEA
RIGHT OUTER JOIN TABLEB (ouch .. hurts just to type that!)
ON ...

says: "select stuff from Table A ... and join to tableB as well. actually, though, give me all rows from TableB, and only rows from tableA that match tableB. " Why are we selecting FROM tableA ? It is just an auxillary table to the query -- the key component of this query is tableB; that's where the primary data comes from.

Isn't this:

SELECT ...
FROM TABLEB
LEFT OUTER JOIN TABLEA ON ...

a little more logical and readable? "give me all rows from tableB, and any rows that match from TableA."

Think about it -- as soon as you see a RIGHT OUTER JOIN it translates to "everything you've read about the FROM clause up to this point is wrong! Start over ..."

logically, you should consider your source tables as your primary "driving" tables of any query, list that primary table as the FROM, after that do your INNER JOINS, and after all THAT do any outer joins that are necessary to return your results.

Writing good FROM clauses with joins logically place is an important key to writing efficient and readable sql statements.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-04 : 14:51:26

I've used RIGHT, but sparingly....well actually I think it was once...

I had to "flip" a massive existance check

I thought it was ok, since I didn't have to muck with moving everything around...

Just make the check payable to C. Ash

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources