Avoid Select *

A recent discussion on CF-Talk implied cfqueryparam as the culprit in a query problem. It seems, however that the problem only happens when using cfqueryparam in conjuction with "select *".

In the discussion, Ben Nadel pointed out reasons he recommends listing out columns instead of using of "select *". His words:

1. Makes the select statement more clear to anyone else reading.
2. Increases speed of the query.
3. Decreases the amount (most likely) of info that SQL has to transfer to the CF memory space.

In his blog entry, Yacoubean mentioned the claim that cfqueryparam was the culprit. In the comments, Mike Kelly pointed out that he has been using cfqueryparam extensively for 3 years and has never experienced the problem mentioned except when using "select *". My experience closely mirrors his.

My recommendation is to avoid "select *" completely. The reasons Ben Nadel stated are my primary reasons as well.

Having worked on code where "select *" is used, I can speak to Ben's first point. It makes code difficult to use without knowing the names of the columns that are returned. Many times, I have had to open the database to see the names of the columns returned from a query using "select *".

The increase in speed by listing columns comes from how the database handles "select *". It first has to find out what columns the table has and then retrieve all of them. I don't think that this represents a major speed issue. It does, however, relate to other problems. In some situations (for example, using cfqueryparam with "select *") the column list can get cached, leading the column list to remain the same even if a new column is added to the table. This was the problem that started the discussion.

If you insist on using "select *" with cfqueryparam, Adam Howitt suggests either diabling "maintain client connections" in cfadmin or (his preference) changing the sql slightly (adding "and 1 = 1" to the where clause, for example) so that the query will be different than the one cached. S. Isaac Dealey suggests that changing "max pooled statements" to 0 in your DSN setting in cfadmin may also eliminate the problem.

I would suggest that avoiding "select *" is your best alternative as it solves all of these problems.

The third issue Ben references has to do with using "select *" when you don't really every column in the table. This isn't a major issue when you are retrieving one record for a detail page or an edit form. It is, however, a major issue when you are retrieving every row for a list page. What happens is that ColdFusion asks for all the data in every column of the table for the selected rows and the database returns all of this data to ColdFusion. This represents a lot of useless network traffic if you are selecting several long text or BLOB fields that you aren't using.

In short, if you are using "select *", don't.