Perform Scalar Aggregates and Still get the Benefit of Batch Processing

Scalar aggregates, (aggregates without a GROUP BY clause, like SELECT COUNT(*) from T) are simple, but ironically they don't work with the CPU-saving batch mode of query execution with columnstore indexes in SQL Server 2012 RTM. Rest assured we're working already to make them run in batch mode in the future. For now, if you want to perform the equivalent of a scalar aggregate with batch mode speed, you can do it with a query rewrite.

As usual, the trick is to write an equivalent query that does most of its work in batch mode, summarizes data to a fairly small number of rows, and then aggregates the resulting rows.

For example, for a 101 million row table dbo.Purchase with a columnstore index on it, the following query uses the columnstore index, but runs in row mode, not batch mode:

select count(*)
from dbo.Purchase

On a 4-core Intel Core i7 laptop, the above query produces these performance statistics using SET STATISTICS TIME ON.

SQL Server Execution Times:
  CPU time = 4555 ms, elapsed time = 1236 ms.

All the times given here are warm start (i.e. the query was run twice and the second time was measured, so there is no I/O cost). 

This execution time is still pretty good. The same query takes over 12 seconds of CPU and 3.3 seconds elapsed on top of a clustered B-tree index. But you can do better. You can rewrite the query as follows and get batch mode execution for most of the work done by the query:

with CountByDate (Date, c)
as (
   select Date, count(*)
   from dbo.Purchase
   group by Date
)
select sum(c)
from CountByDate;

The above query produces identical results, and runs with these performance statistics:

SQL Server Execution Times:
  CPU time = 219 ms, elapsed time = 93 ms.

That's about a 13X speedup over the row-mode plan on the columnstore and a 35X speedup over the B-tree index plan. Not bad for a little query rewrite!

Return to main SQL Server columnstore index tuning page.