What is different between select with aggregate and without it in speed?

Question

I have two SQL queries.

First query:

CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT dateadd(HH, datediff(HH, 0, time_Stamp), 0) as Time1
      ,[Field1]
      ,[Field2]
      ,[Field3]
      ,[Field4]
      ,[Field5]
      ,[Field6]
      ,[Field7]
FROM [dbo].[TableT001]

and second query:

CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Select dateadd(HH, datediff(HH, 0, time_Stamp), 0) as Time1
,Min(cast(Field1 as bigint)) 
,Min(cast(Field2 as bigint)) 
,CAST(Avg(Field3) as float) 
,CAST(Avg(Field4) as float) 
,CAST(Avg(Field5) as float) 
,CAST(Avg(Field6) as float) 
,CAST(Avg(Field7) as float) 
from TableT001 
Group by dateadd(HH, datediff(HH, 0, time_Stamp), 0)  
order by Time1 

The first query executes in 30 seconds, and the second executes in 2 seconds!

While two queries read the same data from TableT001 (about 1450000 rows), what is the cause of difference in speed?

Edit:

• The first query execution plan:

  the first query execution plan

• The second query execution plan:

  the second query execution plan

Answer 1

Normally, aggregation is much slower than not aggregating. However, moving the final result set out of the database also takes time.

The time spent returning 1,450,000 rows is clearly much greater than returning 24 rows. I would speculate that this accounts for much of the difference in your case. In fact, it probably accounts for more of the difference, because the aggregation should be slower.

Aggregation can make use of indexes on the tables, so your database might also have indexes that optimize the aggregation.