SUM() takes less time than SELECT() in SQL. How it works?

Question

I have a SQL table containing 1 million records. When I call

SELECT *
   FROM [AdventureWorksDW2012].[dbo].[LotTable]

it took 13 seconds to complete the query.

I ran the below query to get the SUM() of ID column. It contains random numbers containing plus and minus values.

 SELECT SUM(NewestID)
   FROM LotTable

This query just took 500 Milliseconds. To execute SUM() SQL engine should read the values and apply some operation on it. But how it is running quickly than SELECT(). What is the logic behind? Refer the below images.Thanks in advance.

Answer 1

That is the expected behavior. You see when a you send a query to a database system several things happen:

• the query is analyzed, optimized and an execution schema is designed,
• the query is executed (!),
• the result is communicated to the client (!)

The last two items (with an exclamation mark) are potential sources of speedup.

First of all, if you sum up values, you do not need to store all these values. Indeed, you use an accumulator. So a mature database system will initialize an accumulator with value 0 and then for each row it finds (matching optional constraint), it will add that value to the accumulator. The key is, the accumulator uses a fixed amount of memory. For instance for an integer, that is usually less than 10 bytes. So the accumulator is stored in (fast) memory.

An advantage of SUM(..) is also that it is associative: ((a+b)+c)+d is equal to (a+b)+(c+d). Depending on how the database works and is configured, it can thus distribute the task among several workers that each calculate the sum of a part of the table. These subsums are then summed up together.

If on the other hand you perform a SELECT query, than the result is written row by row. As a result there is linear memory usage: for every row that matches, we need memory. For large tables it is possible that old rows will be "swapped" out of the CPU cache and sometimes even the memory. So it will take longer to execute the query.

Finally the system needs to respond. Now if you perform a SUM(..), that is only one row. So the amount of data that is transferred is small. A SELECT query will usually transfer hundreds of rows. Of course transferring large amounts of data takes more time than small amounts of data.

Answer 2

Short answer (from a DBA's perspective):

It's because SELECT * has to return a lot more columns and rows than SELECT SUM(NewestID).

Also, the SUM() might be running in parallel, which is why it might be faster.

Alsooo, the result set display in SSMS is quite slow and for comparing the actual execution times of the query you can use SET STATISTICS TIME ON at the start of the session. So,

SET STATISTICS TIME ON

SELECT *
FROM [AdventureWorksDW2012].[dbo].[LotTable]

SELECT SUM(NewestID)
FROM [AdventureWorksDW2012].[dbo].[LotTable]

Now move to the Messages tab and take a look at the execution time of your queries:

• CPU time : operations made on CPU, like aggregates (SUM, AVG etc) or other arithmetic operations
• Elapsed Time : CPU time + time needed to store in RAM, send results through memory BUS, network etc.

---

Another thing you can try is to discard the display of the results in SSMS. Go to Tools -> Options -> Query Results -> SQL Server -> Results to Grid and check the "Discard results after execution".

Open a new tab and run your queries again and see how their execution times compare now. (Make sure to uncheck the option so that you do get results back in your other tabs for new sessions).

---

Actually, there might be a lot more factors than these, like if you have an index on NewestID column or if you have (or not) a clustered index on the table.