5

DBMS: SQL Server 2012 Express

I have an SQL Statement which looks something like this:

SELECT

    OverSpeedings = SUM(OverSpeeding),
    OverspeedingPoints = SUM(OverSpeeding) * 5,


FROM
    MyTable

GROUP BY    
        COLUMNS_HERE

I was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Or should I use a CTE with one SUM like the following:

SELECT

    OverSpeedings,
    OverspeedingPoints = OverSpeedings * 5,

FROM
    (
    SELECT

        OverSpeedings = SUM(OverSpeeding)

    FROM
        MyTable

    GROUP BY    
        COLUMNS_HERE    
    ) A
  • Well, you can test! Fill a sample table with a few million rows and time the 2 queries. And then another with say SELECT OverSpeedings = SUM(OverSpeeding), OverspeedingPoints = SUM(OverSpeeding + 0) * 5, ... which should do the calculation twice – ypercubeᵀᴹ Feb 17 '17 at 12:41
  • @ypercubeᵀᴹ I did - The Query time is about the same - so is the Logical Reads, and also the Scan Count – Dawood Awan Feb 17 '17 at 12:44
  • Also add a tag with the dbms you use (and version). Is it SQL Server or something else? – ypercubeᵀᴹ Feb 17 '17 at 12:51
  • SQL Server 2012 - Express – Dawood Awan Feb 17 '17 at 12:51
  • Is there any difference in the query plan? Is your interest in tuning this query? – Erik Darling Feb 17 '17 at 13:29
6

was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Sum will be calculated once and result is used for multiple columns referring the same sum

Below is sample setup from my instance ,which will prove the same

select 
sum(empid) as empidsum,
sum(empid)*2 as empidsum,
sum(empid)*4 as empid4
 from orders

execution plan:
enter image description here

In the above execution plan,highlighted computed scalar shows below

Scalar Operator([Expr1003]*(2))  
Scalar Operator([Expr1003]*(4))  

Where Expr1003 is the sum calculated by previous compute scalar

should I use a CTE with one SUM like the following

CTE is just for readability,using a CTE won't help performance .

There are cases where materialzing CTE will help in improving the query.Look out this answer for more details:

Which are more performant, CTE or temporary tables?

| improve this answer | |
  • 1
    +1 for CTE info. It's easy to forget that the optimizer decomposes the CTE before executing. It can help the human query writer but it means nothing to execution engine. – Fred Shope Feb 17 '17 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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