When using columnstore indexes with SQL Server 2012, using NOT IN (<subquery>) can prevent batch mode query execution, and the query may run more slowly than if batch mode had been used for the bulk of the work of the query.
If you can modify a NOT IN query to produce the same result but do most of the work with batch processing, you can speed it up significantly. Here's an example:
-- This query runs in row mode and takes about 11 seconds on a 4-core processor
-- when dbo.Purchase contains about 101 million rows,
-- and has a columnstore index on it that includes all columns of the table.
-- Find distinct MediaId values present in dbo.Purchase but not in dbo.Media.
-- This equivalent query runs mostly in batch mode
-- and takes about a third of a second.
select distinct p.MediaId
The common table expression (CTE) named "ids" runs in batch mode (and is thus fast). The main SELECT in the query runs in row mode. However, the main SELECT only operates on a few thousand rows, so it runs fast too.
Return to main SQL Server columnstore index tuning page.