Question: What is Faster, SUM or COUNT?
Answer: Both are the same. Let me prove it to you.
I recently visited a big financial technology organization and while working on the day-long engagement on Comprehensive Database Performance Health Check in tuning 5 of their most expensive queries, landed upon the very interesting question by their Senior Developer. During the day-long engagement first part of the day, we spent on tuning their server and the final part of the day in tuning their expensive queries. The cumulative time for all the original five queries was over 3 hours which we were able to bring down to just a total of 6 minutes.
During the conversation, the senior developer pointed out that they are using SUM at one place and wanted to change it to COUNT as they believe that SUM is slower and COUNT is faster. Honestly, in most of the cases, they both are the same if your situation is something like in the following query.
As the senior developer and entire organization did not believe me that SUM or COUNT both perform equally in most of the case when you are counting certain conditions. Here I have created the same situation with the help of the AdventureWorks database.
Let us compare the performance of the SUM and COUNT.
USE AdventureWorks2014 GO SET STATISTICS IO ON GO -- Use of SUM -- Original Query SELECT SUM(CASE WHEN SalesOrderID > 50 THEN 1 ELSE 0 END) FROM [Sales].[SalesOrderDetail] WHERE ProductID > 750 GO -- Use of COUNT -- New Proposed Query SELECT COUNT(CASE WHEN SalesOrderID > 50 THEN 1 ELSE 0 END) FROM [Sales].[SalesOrderDetail] WHERE ProductID > 750 GO
Here is the output of the execution plan:
You can see the execution plan is identical and absolutely the same.
Let us see the output of the statistics.
You can observe that IO read is absolutely the same as well.
I have been doing SQL Server Performance Tuning Consulting for over 10 years and so far when you are counting condition with the CASE expression, SUM or COUNT, both perform absolutely the same in SQL Server.
Please leave your comment, I would love to know your experience.