Does the number of columns returned affect the speed of a query?

Question

If I have two queries

SELECT Id, Forename, Surname
FROM Person
WHERE PersonName Like(‘%frank%’)

And

SELECT *
FROM Person
WHERE PersonName Like(‘%frank%’)

Which query will run faster? Is the where clause / table joining the biggest factor, or the number of columns returned?

I’m asking because I’m building a series of objects that map to database tables. Typically each object will have at least these three methods:

Select – Selects everything

List – Selects enough that is can be used to populate a dropdown list

Search – Selects everything that is viewable in the results, typically about 6 or so columns.

If each query returned exactly the same set of columns the maintenance and testing of the code should be more trivial. The database is unlikely to get above 50,000 rows in any given table, so if the difference in performance is small then I’ll take the saving in development time. If the performance is going to drop through the floor, then I’ll work at in from a different angle.

So, for ease of development, is SELECT * sensible, or is it naive?

Answer 1

You better avoid SELECT *

• It leads to confusion when you change the table layout.
• It selects unneeded columns, and your data packets get larger.
• The columns can get duplicate names, which is also not good for some applications
• If all the columns you need are covered by an index, SELECT columns will only use this index, while SELECT * will need to visit the table records to get the values you don't need. Also bad for performance.

Answer 2

SELECT * is usually never a good idea. It may not slow down your DBMS fetch a lot but it will probably result in more data being transmitted over the network than you need.

However, that's likely to be swamped into insignificance by the use of the LIKE '%frank%' clause which is basically non-indexable and will result in a full table scan.

You might want to consider cleaning up the data as it enters the database since that will almost certainly make subsequent queries run much faster.

If you're after frank, then make sure it's stored as frank and use:

select x,y,z from table where name = 'frank'

If you want to get franklin as well, use:

select x,y,z from table where name like 'frank%'

Both of these will be able to use an index on the name column, "%frank%" will not.