CODEWITHSUNDEEP
sqlcoldfusionqoq
Charles Robertson
Charles Robertson

Reputation: 1820

Using a wildcard operator in a query of query

I have the following code:

<cfquery name="somequery1" datasource="somedsn">
    SELECT somecolumn1, somecolumn2, somecolumn3 
    FROM sometable 
    WHERE someid = <cfqueryparam cfsqltype="cf_sql_integer" value="1">
</cfquery>

<cfquery name="somequery2" dbtype="query">
    SELECT *
    FROM somequery1
</cfquery>

My code manager says I need to change the Query of Query to:

<cfquery name="somequery2" dbtype="query">
    SELECT somecolumn1, somecolumn2, somecolumn3 
    FROM somequery1
</cfquery>

Can someone explain why I would need to redefine the column references in the Query of Query? Surely, the wildcard operator takes care of this.

Is there any technical or performance gain to redefining the column references in the SELECT clause of a Coldfusion Query of Queries? This assumes that the column references have already been explicitly set in the database query that is supplied to the Query of Queries.

I believe the use of the wildcard operator makes the code cleaner and easier to update, because any changes to the column references only need to be done once.

Upvotes: 0

Views: 247

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Here is another approach.

<cfset selectFields = "somecolumn1, somecolumn2, somecolumn3">

<cfquery name="somequery1" datasource="somedsn">
select #selectFields#
etc
</cfquery>

<cfquery name="somequery2" dbtype="query">
select #selectFields#
from somequery1
</cfquery>

You get to use your time wisely and your code manager might actually like it.

Upvotes: -1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

EDIT:

As discussed, yes it is correct that your current code will be more modular considering the fact that it would incorporate any changes(for example if you need to make the changes in the selected columns) in your query ie., it will take care of any columns which you might add in future. So your present query is efficient and good to proceed with.

The wildcard character surely takes care of it if you want to select all the column, however it is nowadays not recommended and usually not preferred to use wildcard character when selecting the columns. You can have a look at Aaron Bertrand Bad habits to kick : using SELECT * / omitting the column list:

But there are several reasons why you should avoid SELECT * in production code:

  1. You can be returning unnecessary data that will just be ignored, since you don't usually need every single column. This is wasteful in I/O, since you will be reading all of that data off of the pages, when perhaps you only needed to read the data from the index pages. It is also wasteful in network traffic and in many cases the memory required by the consuming application to hold the results.
  2. When you use SELECT * in a join, you can introduce complications when multiple tables have columns with the same name (not only on the joined columns, such as OrderID, which are typically the same, but also peripheral columns like CreatedDate or Status). On a straight query this might be okay, but when you try to order by one of these columns, or use the query in a CTE or derived table, you will need to make adjustments.
  3. While applications should not be relying on ordinal position of columns in the resultset, using SELECT * will ensure that when you add columns or change column order in the table, the shape of the resultset should change. Ideally, this should only happen intentionally.

Upvotes: 1

Adam Cameron
Adam Cameron

Reputation: 29870

As you've discussed with Rahul: your "code manager" is offering good advice if this was a DB-based query, but I think it's a bit egregious in the context of a CFML query-on-query.

I suspect they have heard the guidance in the context of DB queries, and have not really thought it through sufficiently when giving guidance on in-memory query operations.

In short: your code is more optimal as it stands than the change's they're advising.

Upvotes: 4

Related Questions