Performance of AVG vs using COUNT and SUM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance of AVG vs using COUNT and SUM

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2012-07-23 : 09:27:31

I want to get the average of a column, but just wondering if there is a performance benefit using SUM and COUNT instead of using AVG?

For example is this:
SELECT SUM(SalePrice), COUNT(SalePrice) FROM ...
(dividing the two in code to get the average) more efficient in SQL Server than:
SELECT AVG(SalePrice) FROM ...
?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-23 : 09:33:54

It'll give you different results if the column is nullable. Example


DECLARE @foo TABLE (
	[Key] INT IDENTITY(1,1)
	, [Value] MONEY NULL
	)

INSERT @foo ([Value])
VALUES (100.00), (50.00), (NULL)

SELECT
	SUM([Value]) AS [Sum]
	, COUNT(*) AS [Count]
	, AVG([value]) AS [Avg]
	, SUM([Value]) / CAST(COUNT(*) AS FLOAT) AS [CalcAvg]
FROM
	@foo

and it's *much* more declarative to ask for the AVG rather than doing it yourself.

I guess it *may* be possible that it'll be slower if you already need the sum and count but... I'd be surprised you'd ever notice it.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-23 : 09:34:20

results from above


(3 row(s) affected)
Sum                   Count       Avg                   CalcAvg
--------------------- ----------- --------------------- ----------------------
150.00                3           75.00                 50
Warning: Null value is eliminated by an aggregate or other SET operation.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

waveform
Yak Posting Veteran

93 Posts

Posted - 2012-07-23 : 12:13:52

Thanks Charlie, but in my example I'm using COUNT(colname), not COUNT(*).

COUNT(colname) does not count NULL columns, so theoretically I should get the same result in the end.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-23 : 12:38:09

OK -- so you *can* calculate the average that way but why would you?

My gut feeling is that it will be no faster but that's not enough so I tested it.
Data Set:


IF OBJECT_ID('tempdb..#sample') IS NOT NULL DROP TABLE #sample
GO

CREATE TABLE #sample (
	[key] INT IDENTITY(1,1) PRIMARY KEY
	, [Value] MONEY
	)
GO
	
; WITH power10 AS (
                   SELECT 1 AS [n] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
             UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
             )
INSERT #sample ([value])
SELECT  CAST(CAST(NEWID() AS VARBINARY(4)) AS MONEY)
FROM           power10 AS a
	CROSS JOIN power10 AS b
	CROSS JOIN power10 AS c
	CROSS JOIN power10 AS d
	CROSS JOIN power10 AS e
	CROSS JOIN power10 AS f
	CROSS JOIN power10 AS g

1 million random prices.

And when I run some queries:


SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT AVG([value]) FROM #sample

-- CPU time = 1980 ms,  elapsed time = 316 ms.
-- CPU time = 2064 ms,  elapsed time = 259 ms.
-- CPU time = 2063 ms,  elapsed time = 245 ms.
-- CPU time = 2078 ms,  elapsed time = 242 ms.
-- CPU time = 1968 ms,  elapsed time = 202 ms.

SELECT SUM([Value]) FROM #sample
-- CPU time = 2015 ms,  elapsed time = 199 ms.
-- CPU time = 2079 ms,  elapsed time = 272 ms.
-- CPU time = 1953 ms,  elapsed time = 189 ms.
-- CPU time = 2421 ms,  elapsed time = 229 ms
-- CPU time = 1702 ms,  elapsed time = 268 ms..

SELECT SUM([Value]) / COUNT([Value]) AS [Avg] FROM #sample
-- CPU time = 1889 ms,  elapsed time = 283 ms.
-- CPU time = 2049 ms,  elapsed time = 235 ms.
-- CPU time = 2045 ms,  elapsed time = 217 ms.
-- CPU time = 1845 ms,  elapsed time = 242 ms.
-- CPU time = 1875 ms,  elapsed time = 218 ms.
-- Table '#sample_0000000050FC'. Scan count 25, logical reads 26018, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SELECT SUM([value]), COUNT([value]), AVG([Value]) FROM #sample
-- CPU time = 2281 ms,  elapsed time = 286 ms.
-- CPU time = 2296 ms,  elapsed time = 265 ms.
-- CPU time = 1956 ms,  elapsed time = 246 ms.
-- CPU time = 2013 ms,  elapsed time = 248 ms.
-- CPU time = 1954 ms,  elapsed time = 251 ms.

-- Table '#sample_0000000050FC'. Scan count 25, logical reads 26018, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I get *completely* dominated by the IO subsystem.... completely.

I could run this on a fusionIO box but.... meh....

I don't think there is anything to be gained by doing the sum / count and then working out the average yourself.

Maybe, just maybe, if you needed to report the sum and count anyway then you could do in via a derived table but.... I'd be surprised if there was any difference in performance.

What size and shape your data?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-23 : 23:17:47

Ask your self this: if was faster to do it in this way
a) Why would AVG even exist
b) Why would they not implement AVG as SUM/COUNT

As it happens it would be crazy not to take advantage of knowing in advance what you are trying to do (average) rather than sum & count.
You can always check the plan too.

waveform
Yak Posting Veteran

93 Posts

Posted - 2012-07-24 : 01:04:55

Thanks for all the help and advice.

It's a survey system, with an Answer table that contains answer scores linked to questions and participants.
AnswerId INT IDENTITY
QuestionId INT
ParticipantId INT
Answer SMALLINT

The "Answer" column is the score of the answer a person chose, usually from 1 to 10. It just crossed my mind whether COUNT & SUM were quicker, but yes I should have just tried it in the query analyser. Bit new to that, so thought I'd ask. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources