Why an union is faster than a group by

Question

Well, maybe I am too old school and I would like to understand the following.

query 1.

select count(*), gender from customer
group by gender

query 2.

select count(*), 'M' from customer
where gender ='M'
union
select count(*), 'F' from customer
where gender ='F'

the 1st query is simpler, but for some reason in the profiler,when I execute both at the same time, it says that query 2 uses 39% of the time, and query 1, 61%.

I would like to understand the reason, maybe I have to rewrite all my queries.

Answer 1

Your query 2 is actually a nice trick. It works like this: You have an index on gender. The DBMS can seek into that index two times to get two ranges of rows (one for M and one for F). It doesn't need to read anything from these rows, just that they exist. It can count the number of rows that exist in the two ranges.

In the first query the DBMS needs to decode the rows to read the gender, then it needs to either sort the rows or build a hashtable to aggregate them. That is more expensive than just counting rows.

Answer 2

Are you sure? Maybe the second query is just using cached resources from the first on.

run them in two separately batches and before each one run DBCC FREEPROCCACHE to clean the cache. Then compare the values of each execution plan.