Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Often in querying my MS SQL Server databases, I need to create a calculated field, such as this

(CASE WHEN A.type = 'Workover' THEN 'Workover' 
      ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' THEN 'Drilling' 
                 WHEN substring(C.category, 2, 1) = 'C' THEN 'Completion' 
                 WHEN substring(C.category, 2, 1) = 'W' THEN 'Workover' 
                 ELSE 'Other' 
            END)
END)

and then I need to group my results by this calculated field (among others). Hence, I have the same calculation in both the SELECT and GROUP BY clauses. Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?

share|improve this question
3  
It's smart enough. As long as the two expressions are identical. –  ypercube Feb 7 at 9:16
    
Can you provide documentation of this? I'm posting this question here on SO because I was unable to do so. –  Dr. Drew Feb 7 at 9:33
    
Does MS SQL support AS? –  11684 Feb 7 at 12:12
    
@11684: It does, why? –  Andriy M Feb 7 at 12:19
1  
As I mentioned somewhere below, even if the expression is aliased in the SELECT clause, the alias can't be used in the GROUP BY clause. –  Dr. Drew Feb 7 at 13:52
show 2 more comments

migrated from stackoverflow.com Feb 7 at 11:14

This question came from our site for professional and enthusiast programmers.

3 Answers

I have the same calculation in both the SELECT and GROUP BY clauses. Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?

The simple answer is that SQL Server makes no general guarantees about when, and how many times, a scalar expression will be evaluated at execution time.

There are all sorts of complicated (and undocumented) behaviours within the optimizer and execution engine regarding the placement, execution, and caching of scalar expressions. Books Online doesn't have much to say about this, but what it does say is this:

Compute Scalar description

This describes one of the behaviours I alluded to before, deferred execution of expressions. I wrote about some of the other current behaviours (which could change any time) in this blog post.

Another consideration is that the cost model used by the query optimizer doesn't currently do much in the way of cost estimation for scalar expressions. Without a robust costing framework, current results are based on broad heuristics or pure chance.

For very simple expressions, it probably doesn't make much difference whether the expression is evaluated once or many times in most cases. That said, I have encountered large queries where performance has been adversely effected when the expression is redundantly evaluated a very large number of times, or the evaluation occurs on a single thread where it would have been advantageous to evaluate in a parallel branch of the execution plan.

In summary, the current behaviour is undefined, and there is nothing much in execution plans to help you figure out what happened (and it will not always be convenient to attach a debugger to examine the detailed engine behaviours, as in the blog post).

If you encounter cases where scalar evaluation issues matter to performance, raise the issue with Microsoft Support. This is the best way to provide feedback to improve future versions of the product.

share|improve this answer
add comment

As the comment on your question states, the answer is (in my experience, at least) "yes". SQL Server is generally smart enough to avoid re-computation. You could probably verify this by showing the execution plan from within SQL Server Management Studio. Each calculated field is designated Exprxxxxx (where xxxxx is a number). If you know what to look for, you should be able to verify that it uses the same expression.

To add to the discussion, your other aesthetic option is a common table expression:

with [cte] as
(
    select
        (case when a.type = 'workover' then 'workover' else 
        (case when substring(c.category, 2, 1) = 'd' then 'drilling'
              when substring(c.category, 2, 1) = 'c' then 'completion'
              when substring(c.category, 2, 1) = 'w' then 'workover'
              else 'other' end)
         end)) as [group_key],
         *
    from
        [some_table]
)
select
    [group_key],
    count(*) as [count]
from
    [cte]
group by
    [group_key]

Short answer, they are functionally identical to a view, but are valid only for use in the very next statement. I see them as mostly a more readable alternative to derived tables because it avoids nesting.

Though not relevant to this question, they can reference themselves and in that way be used to construct recursive queries.

share|improve this answer
    
@Quick Joe Smith: I would think you're correct about the Exprxxxxx, since I also have seen that. However, if I give a name to the expression manually (case ... end) as OpType, then use the field OpType in the GROUP BY clause, I get an error that it's an invalid column name. –  Dr. Drew Feb 7 at 11:16
    
Unfortunately, oftentimes your only way out of specifying the expression twice is to use one of the above methods: a CTE, view or a nested query. –  Quick Joe Smith Feb 7 at 11:44
2  
Unless you also know about CROSS APPLY. –  Andriy M Feb 7 at 11:51
    
Using cross apply in this case is a bit of a stretch, and it would very likely harm performance by introducing a needless self-join. –  Quick Joe Smith Feb 8 at 1:05
2  
I don't think you "got" the suggestion. The CROSS APPLY just defines the alias from columns in the same row. No need for a join. e.g. SELECT COUNT(*), hilo FROM master..spt_values CROSS APPLY (VALUES(high + low)) V(hilo) GROUP BY hilo –  Martin Smith Feb 8 at 7:19
add comment

Performance is just one aspect. The other is maintainability.

Personally, I tend to do following:

SELECT T.GroupingKey, SUM(T.value)
FROM
(
    SELECT 
        A.*
        (CASE WHEN A.type = 'Workover' THEN 'Workover' ELSE 
        (CASE WHEN substring(C.category, 2, 1) = 'D' THEN 'Drilling' WHEN substring(C.category, 2, 1) = 'C' THEN 'Completion' WHEN substring(C.category, 2, 1) = 'W' THEN 'Workover' ELSE 'Other' END)
        END) AS GroupingKey
    FROM Table AS A
) AS T

GROUP BY T.GroupingKey

UPDATE:

If you don't like to do nesting, you could create VIEW for each table where you need to use complex expressions.

CREATE VIEW TableExtended
AS 
SELECT 
    A.*
    (CASE WHEN A.type = 'Workover' THEN 'Workover' ELSE 
    (CASE WHEN substring(C.category, 2, 1) = 'D' THEN 'Drilling' WHEN substring(C.category, 2, 1) = 'C' THEN 'Completion' WHEN substring(C.category, 2, 1) = 'W' THEN 'Workover' ELSE 'Other' END)
    END) AS GroupingKey
FROM Table AS A

Then you could do select without doing extra nesting;

SELECT GroupingKey, SUM(value)
FROM TableExtended
GROUP BY GroupingKey
share|improve this answer
    
Actually, another aspect is readability. I'm well aware I could just use a nested query. In my relevant cases, what I've shown is part of a large SQL statement / script. I don't want to add extra nested queries and make it more difficult to read if I don't have to. –  Dr. Drew Feb 7 at 9:09
    
Although I don't agree to you that doubling complex expressions would make readability better than while using nested queries, I added another way to get over those issues without creating extra nestings. –  Kaspars Ozols Feb 7 at 9:17
    
I too don't agree with @Drew.Kaspras 's queries and idea are simple ,maintainable and readable also performance wise it is superior. though performance is altogether a different thing .we need to know the whole story and why you are doing that way in first place . –  KumarHarsh Feb 7 at 9:57
    
"we need to know the whole story and why you are doing that way in first place" I did not provide all this information, because I wanted an answer simply to the question I posed - if SQL server would not recalculate. –  Dr. Drew Feb 7 at 11:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.