The vector-oriented batch processing mode of columnstore indexes gives the best performance. In some cases, the query optimizer reverts to row-mode processing. This still saves I/O time but doesn't save much or any CPU time. Outer joins are not supported in batch mode in SQL Server Denali. To hear how to work around the problem, and get fast performance for outer join logic, see the following video:

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI312

Scroll to minute 49 in the video and watch about 5 minutes from that point.

The key idea is to use an inner join with grouping and aggregation to do most of the work, and produce a small result rowset. Then use an outer join later to generate output rows for dimension table values that don't join to any fact table rows.

Consider the following queries, where dbo.Purchase is a large (101 million rows) fact table with a columnstore index, and dbo.Media is a dimension table with about 7,000 rows. Query Q1 doesn't use batch processing because of the left outer join. It takes about 15 seconds in the video. Q2 uses a regular (inner) join instead of an outer join. It runs in sub-second time. But the result doesn't contain rows for Media dimension values which don't join to any Purchase rows.

Q3 is a rewrite of Q1 that gets the same result as Q1 but uses batch processing, and runs in sub-second time. The key idea is to use a subquery that uses only inner join that groups and aggregates the data down to a small number of rows. Then later, outer join that with Media dimension rows to produce output rows for those Media rows that don't join to any Purchase rows.

-- Q1: no batch processing
select m.MediaId, COUNT(p.IP) PurchaseCount
from dbo.Media m left outer join dbo.Purchase p on p.MediaId=m.MediaId
group by m.MediaId
order by COUNT(p.IP) desc

-- Q2: batch processing enabled (different result than Q1)
select m.MediaId, COUNT(p.IP) PurchaseCount
from dbo.Media m join dbo.Purchase p on p.MediaId=m.MediaId
group by m.MediaId
order by COUNT(p.IP) desc;

-- Q3: Q1 query rewrite, same result, but with batch processing.
-- This relies on the fact that m.MediaId is constrained to be not null.
with T (MediaId, PurchaseCount) as (
  select m.MediaId, COUNT(p.IP) PurchaseCount
  from dbo.Media m join dbo.Purchase p on p.MediaId=m.MediaId
  group by m.MediaId
)
select m.MediaId, ISNULL(T.PurchaseCount,0)
from Media m left outer join T on m.MediaId=T.MediaId
order by ISNULL(T.PurchaseCount,0) desc; 

The query plan for Q1 doesn't use batch processing, as you can see here in a showplan diagram of the rightmost part of the plan (click the diagram to expand it):


When you hover the mouse pointer over the columnstore index scan operator, the yellow tooltip appears. The Estimated Execution Mode is shown as Row, indicating the Row mode was chosen and not Batch mode.

The query plan for Q3 does use batch processing for the expensive part of the query (the one the processes the large number of rows from the fact table, dbo.Purchase). The following is this portion of the query plan from Q3:

If you need to use outer join logic but want to get the full performance benefits of batch processing, you can rewrite your queries using a style similar to the one used in Q3.

If the join columns of the tables being OUTER JOINed are nullable, you will need to add an extra GROUP BY and aggregate, shown in bold below, so the original OUTER JOIN query and the rewrite return the same results:

with T (MediaId, PurchaseCount) as (
   select m.MediaId, COUNT(p.IP) PurchaseCount
   from Media m join Purchase p on p.MediaId=m.MediaId
   group by m.MediaId
)
select m.MediaId, SUM(ISNULL(T.PurchaseCount,0)) as PurchaseCount
from Media m left outer join T on m.MediaId=T.MediaId
group by m.MediaId
order by SUM(ISNULL(T.PurchaseCount,0)) desc; 

Return to main columnstore performance tuning page.