Faster with SELECT * or SELECT Field1, etc.?[Resolved]

I'm wondering if is faster to select all the fields from a db and just not use the ones you don't want, or to pick out the specific fields you're going to use.

I have a table with ~20 items. I print a report with about 10 of them. Is it faster to do a SELECT * or to do SELECT Field1, Field2, etc (just the fields I want)?

Edit: Also, this is for several hundred records and not necessarily an ADO/DAO question... I'm actually using MS SQL Server and PHP. I'm wondering more about the theory than the actual working of it.

The less data you make it retrieve, the faster it should be.

You should only bring back what you will need anyway.

ober5861

It is always faster to only transfer the data that you actually need. Time needs to be taken to retrieve the data and format it to send it to you. Fields that you do not need will take time, Not to mention memory.

But they way I'm thinking, if it has to go and decide which ones to grab, then that takes processor time. I have to think that it would take less processing to just grab them all!?

And like I said, I'm only throwing out a handful of fields.

I have noticed a speed difference in the past between using "Select *" and "Select Field1, Field2, ...Field9" (all fields), even though the data is exactly the same in both cases.

I'm guessing that when you send "select *" to the server it needs to convert this to individual fields, and the local software (ADO/DAO/ODBC etc) needs to do the same to recieve the data.

Quite why it would make such a difference I dont know, but in my experience it is definitely faster to name all of the fields!

Alright... thanks for the input fellas! :)

:thumb:

No, it won't be a stored procedure because the WHERE in the query will change.

ober5861


Think of it like this... Suppose it takes 1 second to get one fields information and you have 100 fields, This would take 100 seconds.

If you only want one field, they can not be the same.

Yeah. I got it. Thanks.