SQL Server aggregate performance

Question

I am wondering whether SQL Server knows to 'cache' if you like aggregates while in a query, if they are used again.

For example,

Select Sum(Field),
       Sum(Field) / 12
From   Table

Would SQL Server know that it has already calculated the Sum function on the first field and then just divide it by 12 for the second? Or would it run the Sum function again then divide it by 12?

Thanks

score 9 · Accepted Answer · edited May 23 '17 at 10:08

It calculates once

Select
   Sum(Price),
   Sum(Price) / 12
From
   MyTable

The plan gives:

|--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]/(12.)))
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
     |--Stream Aggregate(DEFINE:([Expr1010]=Count(*), [Expr1011]=SUM([myDB].[dbo].[MyTable].[Price])))
        |--Index Scan(OBJECT:([myDB].[dbo].[MyTable].[IX_SomeThing]))

This table has 1.35 million rows

Expr1011 = SUM
Expr1003 = some internal thing to do with "no rows" etc but is Expr1011 basically
Expr1004 = Expr1011 / 12

Thanks, picked this one for the plan. – Jul 01 '11 at 10:23 — , Jul 01 '11 at 10:23

score 3 · Answer 2 · answered Jun 28 '11 at 14:25

3

According to the execution plan, it doesn't re-sum the column.

answered Jun 28 '11 at 14:25

Neil Knight

47,437
25
129
188

score -3 · Answer 3 · answered Jun 28 '11 at 14:24

-3

good question, i think the answer is no, it doesn't not cache it.

I ran a test query with around 3000 counts in it, and it was much slower than one with only a few. Still want to test if the query would be just as slow selecting just plain columns

edit: OK, i just tried selecting a large amount of columns or just one, and the amount of columns (when talking about thousands being returned) does effect the speed.

Overall, unless you are using that aggregate number a ton of times in your query, you should be fine. Push comes to shove, you could always save the outcome to a variable and do the math after the fact.

answered Jun 28 '11 at 14:24

Limey

2,642
6
37
62

-1 - The time could be an indication of how long it takes to render the results, or pull the 3000 occurances of the same value from memory, and has no indication of if the result is cached or not. – JNK Jun 28 '11 at 14:29
OK but the first line of your answer is still factually and demonstrably incorrect, so the downvote stays. – JNK Jun 28 '11 at 14:37
"I think the answer is no", you take that as a statement of fact? – Limey Jun 28 '11 at 14:42
1

regardless of how you spin it, "no" is an incorrect answer to this question. – JNK Jun 28 '11 at 14:43

SQL Server aggregate performance

3 Answers3