0

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.

animuson
  • 53,861
  • 28
  • 137
  • 147
Charles Robertson
  • 1,760
  • 16
  • 21
  • I have been told that a developer might not understand which columns are being selected. Are you kidding me. Where has this developer come from? Sesame Street??? – Charles Robertson Sep 23 '15 at 06:51
  • One reason I can think of is that in future your main query might include more columns which may or may not need in the QoQ. – Tushar Bhaware Sep 23 '15 at 07:05
  • But that is precisely what I want. I want the Query of Query to reflect any changes in the DB query, without having to touch the Query of Query. This makes it more modular. – Charles Robertson Sep 23 '15 at 07:09
  • As long as you select your columns in the DB query, there is no way the Query of Queries can fail. The important point here, is that the columns need to be selected in the DB query. – Charles Robertson Sep 23 '15 at 07:12
  • 1
    If you always want QoQ to reflect changes in DB query, then there is nothing wrong with your code. – Tushar Bhaware Sep 23 '15 at 08:14
  • *a developer might not understand which columns are being selected* Strictly from a readability/intuitiveness POV that is a reasonable thought. Having maintained some truly hideous legacy apps which used `SELECT *` - *everywhere* (including JOINs) - it would have saved maintenance time had the developers used explicit column names instead. That said, if the queries/qoq's are specifically designed to reflect changes, then obviously it would defeat the purpose. So of course using `SELECT *` makes more sense, but again .. it all depends. – Leigh Sep 23 '15 at 15:53
  • I've used your approach as recently as this morning. However I have a different reason - laziness. – Dan Bracuk Sep 23 '15 at 16:14
  • @Dan Bracuk The important part here, is that, column references are explicitly named in the DB query. If this is done as my example shows, then it is perfeclty safe to use the wildcard operator in the Query of Queries. It seems that certain comments are deviating from facts pertaining to this specific example. For instance, there is no table JOIN in this example... – Charles Robertson Sep 24 '15 at 12:04
  • I believe that there is a correct answer to this question, as has been demonstrated by Adam Cameron, who is considered an expert in the Coldfusion Community. This is primarily a Coldfusion question and not a SQL question. Please reopen this question. – Charles Robertson Sep 24 '15 at 12:18
  • The question is closed because it's primarily opinion based. As much as you and those who upvoted Adam's answer like it, it looks like an opinion to me. – Dan Bracuk Sep 24 '15 at 16:13
  • Whether it is a single table or a JOIN makes little difference. Specifying columns explicitly is more intuitive and "readable" than `SELECT *`, as the code manager pointed out. However, as already mentioned above that is not the sole factor in deciding whether or not it is appropriate. It depends. – Leigh Sep 24 '15 at 21:08
  • A JOIN does make a difference because it obfuscates the fact that there might be an ambiguous column reference – Charles Robertson Sep 25 '15 at 11:34
  • @Dan Bracuk Everything is opinion based to a certain extent. The fact is that I have asked a clear, technical based question. And as we can see from the responses, there is clear support for a particular approach on this, which would suggest that the question has a definable answer... – Charles Robertson Sep 25 '15 at 11:43
  • @Leigh The readability issue is addressed in the DB query, where I have created my column references. The problem with defining the column references a second time, is that the rouitine requires more updating, and is therefore less modular. I would only use this approach with a Query of Queries. It is important that people coming from an SQL background, understand this point. – Charles Robertson Sep 25 '15 at 11:48
  • @Dan Bracuk I have edited this question. Can you please vote for it to be reopened. I really think this issue will help a lot of people coming from a Coldfusion SQL background. And lets be honest, you have shown a lot of interest in this question yourself, so it can't be all that bad... – Charles Robertson Sep 25 '15 at 13:02
  • 1
    @CharlesRobertson - Oh, I understand, and in this case I agree with your reasoning. Just suggesting why it might not be as obvious to the code manager. It is not a matter of absolutes. It is about selecting the option with the best *balance* of pros/cons. If the negative impact of caused by extra maintenance, outweighs minor readability benefits, then using `select *` strikes the best balance. No it is not the "perfect" solution (if such a thing exists), but given that everything has trade-offs, it would be the more "optimal" path. – Leigh Sep 25 '15 at 16:23
  • *it obfuscates the fact that there might be an ambiguous column reference* Yes, but that can also cause technical problems (ie which column does CF return?). Just guessing, but I think the code mgr meant readable in general. Obviously code is more intuitive if it is clear which objects are involved by looking at it. Sure, any decent programmer can find out, but when an application has a lot of queries (modules, libs, etcetera) that kind of thing quickly becomes a factor. So again, it is a reasonable concern. Whether or not that should outweigh other factors is a different matter.. – Leigh Sep 25 '15 at 17:27
  • @Leigh, I think the problem is that my code manager did not understand the context thoroughly enough to make an accurate decision on this matter. – Charles Robertson Sep 26 '15 at 17:32

3 Answers3

4

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.

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • 3
    Yeah, I think you'll get more attention from ppl knowing SQL than you will ColdFusion, due to the comparative size of the communities. And many people won't bother reading (or understanding) exactly what you're asking, if they're not coming from the CFML perspective. – Adam Cameron Sep 23 '15 at 09:35
  • It's great to have validation from someone who really knows about Coldfusion. – Charles Robertson Sep 23 '15 at 09:38
  • I am a liitle dissappointed to see that this question has been put on hold, because it has been deemed a closed question. I strongly believe that there is a definitive answer to this question, or at least an answer that is more correct than any other answer. I also believe that this is an important issue that should be resolved, so that others do not have to experience the possible repercussions of not following ill informed advice from one's peer.. – Charles Robertson Sep 24 '15 at 12:38
  • 1
    It's a good question, but it doesn't fall within what StackOverflow considers appropriate for its purview. This is a fault on the part of S/O, not on your part. – Adam Cameron Sep 24 '15 at 13:06
  • This is kind of draconian. If people find the exploration of this issue, valuable, then for me, that should be enough to keep the question open. It has generated 64 views, over 20 comments & three answers. And, furthermore, this is definitely a question. Admin should note the use of question marks... – Charles Robertson Sep 24 '15 at 15:16
  • 1
    I didn't mean to suggest it was a sensible policy. Just that... it's the policy. I don't agree with it: in fact I think it's bloody stupid, and demonstrates a very narrow-minded view of where the benefits in technical communications lie. – Adam Cameron Sep 25 '15 at 07:46
  • Yes. I agree with your opinion on this matter. I think you mistook 'Admin' for 'Adam', in my previous comment... – Charles Robertson Sep 25 '15 at 11:38
1

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.
Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Yes. But I am already selecting specific columns in the database query. The Query of Query just reads those columns using the * operator – Charles Robertson Sep 23 '15 at 06:40
  • @CharlesRobertson:- I would say that it is just the matter of preference as now developers avoid the usage of wildcard character as much as possible. – Rahul Tripathi Sep 23 '15 at 06:42
  • Sorry Rahul, but you need to look at the context of this code. I am passing a DB query into a Query of Query. I am already selecting the columns in the DB query, so your comments are irrelevant in this context... – Charles Robertson Sep 23 '15 at 06:54
  • Actually Rahul, I would say it is more efficient in this context to use the wildcard operator, because when/if you need to change the selected columns in the DB query, you would only need to change this clause once. I also think it is cleaner to read. – Charles Robertson Sep 23 '15 at 07:06
  • @CharlesRobertson:- Agreed but as I said above, it is just the matter of preferrence. I think your code manager wants that in this way. – Rahul Tripathi Sep 23 '15 at 07:08
  • No. This is not just about semantics, this is about modularity. Which is more efficient? – Charles Robertson Sep 23 '15 at 07:17
  • If you want to add a column to the DB query, and you use my code manager's approach, you have to add it once to the DB query and once to the Query of Query. This is wasteful. – Charles Robertson Sep 23 '15 at 07:19
  • 1
    @CharlesRobertson:- As you pointed correctly, that it is definitely more modular to use `*` as it will take care of any changes that will happen in your columns. – Rahul Tripathi Sep 23 '15 at 07:19
  • I don't suppose you would mind writing the comment that you wrote above [the one I recommended] as the answer. I will then tick it. – Charles Robertson Sep 23 '15 at 07:56
  • What I am trying to say is that your original answer was correct as a general principle, but it was not correct for this specific context of selected columns in a DB query into a QofQ... – Charles Robertson Sep 23 '15 at 07:59
  • 1
    @CharlesRobertson:- Updated my answer and added the part which actually answers your point. Thanks! – Rahul Tripathi Sep 23 '15 at 08:58
  • It's important to note that you're answering this from the perspective of *DB-driven* SQL, whereas the question is asking about a technique CFML has for using SQL to modify record set variable, already in local memory, so a lot of the usual guidelines around `SELECT *` don't necessarily apply so much. – Adam Cameron Sep 23 '15 at 09:37
  • 3
    @CharlesRobertson:- You are welcome. However I agree with Adam that mostly your question will attract the attention because of SQL driven.(*Honestly speaking I too was driven with that*) But looking into the CFML point, it makes more sense to what we discussed – Rahul Tripathi Sep 23 '15 at 09:38
-1

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Actually, he wanted the column references explicitly set in both the DB query and the Query of Query. I asked him about using a variable for the column list, and he said that it might cause readability issues. This was an approach that I had considered though. I still don't think it is as clean as using the wildcard operator in the Query of Query, though, but nice try... – Charles Robertson Sep 24 '15 at 12:11
  • 1
    Where this approach really comes in handy is when your Q of Q is a union query. – Dan Bracuk Sep 24 '15 at 16:11