SELECT * versus SELECT named columns

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

Our Project Manager hates SELECT *, he wants us to list all columns in our
SQL. Does this make a big difference in Access? The PM is mainly a SQL Server
person and apparently it makes a big difference there, but does it in Access?
He explained that with SELECT * the database engine has to enumerate the
columns each time the query is run instead of being able to precompile them.
Nonsense or fact? I like SELECT * because it greatly eases maintenance.

Thanks.
 
It depends on a lot of factors. How many fields are there in the record?
How many of the fields are indexed fields? The more index fields there are,
the slower it gets. If you have a lot of indexed fields he is correct.
However if there are less than 65 fields and four or less are indexed, you
are correct.

Remember Microsoft and IBM are worlds apart in how they attach their fields
to the primary key. In Microsoft each field is a file, in IBM the fields
stay with the record. What system is better will be argued about until the
end of time.

Good Luck

Antonette
 
Thanks for your input. Where does IBM come in? I never mentioned them. SQL
Server is a Microsoft product.
 
IBM also has SQL server or its own server now. I am not sure how they handle
the fields but I have used MS SQL on their AS 400's and it works great!

I am a lot more familiar with it on an all encompassing MS System, but there
are a lot of people using it on the AS 400. Our educational regional office
in Texas has it on their system. I did not know what kind of hardware you had.

Good Luck

Antonette
 
Antonette said:
IBM also has SQL server or its own server now. I am not sure how
they handle the fields but I have used MS SQL on their AS 400's and
it works great!

I am a lot more familiar with it on an all encompassing MS System,
but there are a lot of people using it on the AS 400. Our
educational regional office in Texas has it on their system. I did
not know what kind of hardware you had.
Click to expand...

IBM has UDB400 (unversal database 400) as an integral part of the operating
system and has had this for many years. It is not SQL Server, but it is a
database engine that runs on a server. It is similar to DB2 and over the
years with each OS upgrade the differences between UDB400 and DB2 have
gotten smaller, but it is still different enough to require a different
driver.

SELECT * is less efficient as the db engine has to access the system tables
to get the field names. Whether the difference is significant enough to
matter is questionable, but I have seen papers from IBM that recommend
explicit field names over the *.

When *most* people discourage the use of SELECT * it is because they assume
you are grabbing all fields when you might need only some of them. If you
actually DO need all of the fields then the only downside is the system
table lookup described above and on modern hardware I doubt that a typical
query would show a measurable difference in speed.
 
hi,
I was more interested in the difference in MS Access.
Click to expand...
This is quite the same in Access. But there are two things more to mention:

1) * vs. a field list is like late binding vs. early binding in code.
When you use a field list and you have specified an invalid field name -
maybe it was dropped - then you will get an error at the origin.
Otherwise you may get an error sometimes later, which needs tracking.

2) People tend to asssume an order when using *. This is wrong.


mfG
--> stefan <--
 
Back
Top