Today on MythBusters: Select *

Yesterday I noticed an entry on the Six Signs blog about some security issues on a fairly well known server. I don't know who posted it unfortunately because their blog doesn't show the author's name. He pointed out several rather glaring security issues on the site in question like not scoping and the lack of cfqueryparam tags. There is however one comment he made which I feel is something of a myth in the software community and I'd like to take a minute to address that myth.

The myth is this: that using "select *" in the syntax of a query will slow the query down.

Here's the quote from SixSigns:

First off, one should never use the "*" for getting back all the columns of the database. This is slowing the database server down and is a poor man coding style. In todays tool environment there is no excuse for using the "*" anymore (I am talking for production use here).

Now I believe this is actually a pretty subtle myth. Like most myths I think there's an element of truth to it. The issue is that the phrasing of the statement is misleading. The way it was described on the SixSigns blog as in many cases would lead you to believe that the wild card is slower than a comparable query in which you explicitly declared all the columns in the table. In other words, the myth is not that selecting a smaller number of columns is faster, but that selecting the same columns explicitly is faster than selecting them with the wild card.

So to test this I created a new table in a SQL Server database I had handy. I chose to make it a particularly wide table (at least for me) with twenty columns. For odd numbered columns I created an integer column and inserted a random number. For even numbered columns I created a 35-character string and inserted a UUID. I then inserted about 8 thousand records into my new table and I executed a few select statements against the data. These select statements had the following syntax:

I alternated the list of columns in the select clause using either the wild card (*), the list of all 20 columns or a list of just the first 10 columns. I then executed each query 100 times and displayed the average time for each query. I added the where clause in a second round of tests.

The end result is conclusive. A query using the wildcard (*) executes at exactly the same speed as a comparable query (in my test this was about 200ms). Even after adding indexes, the only thing that sped up this query was reducing the number of columns or rows returned. Returning only the first 10 columns resulted in a query that executed in exactly half the time (100ms). As you might expect, the overhead is similar for each column. (Note that I didn't test CLOBs and the overhead for those would probably be much more significant.) Reducing the number of rows returned scaled the query down in a similarly linear fashion, cutting the time in half again when returning only half the records.

So if you're fetching data from a given table and you really do need all the columns (as is often the case for the CRUD forms an ORM tool might be designed to manage), then there's no reason to avoid using the wild card (*). Most of the time there's also no specific reason to avoid using the wild card if you're returning data even from a big query if you're going to be using most of the columns in the table (i.e. listing names and phone numbers of contacts). There might be some exceptions in cases where the table has a lot of columns but just one or two of those columns happens to be a CLOB that you're not going to use. However especially for tables with small numbers of columns, such as look-up tables (or for example category tables), there's no inherent advantage to explicitly declared columns. They aren't any faster, meaning that select * is viable even in a production environment in many cases.

This myth is BUSTED.

The case in which you want to make sure that you do explicitly declare your columns is when you only want a small amount of the data in a given table. If you only need a name and a date from a table with lots of columns or if there's a CLOB you won't be using, then yes absolutely, explicitly declare the columns you want.

EDIT: Apparently I declared this busted a bit too early. See the continuation here.

p.s. For those who might be interested, I'm adding the CF script I used to test this myth as an enclosure on this entry. You should see a download link just below this text.