12

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?

1

3 Answers 3

15

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.

4

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.

5
  • @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. Andrew
    Feb 7, 2014 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. Feb 7, 2014 at 11:44
  • 2
    Unless you also know about CROSS APPLY.
    – Andriy M
    Feb 7, 2014 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. Feb 8, 2014 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 Feb 8, 2014 at 7:19
2

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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