2014 Columnstore Indexes

SQL Server 2012 introduced columnstore indexes, which can greatly decrease query processing time in OLAP databases. Basically, data is stored by columns rather than by rows, which can allow for faster querying for aggregates and totals. Storing by column also allows for greater compression of the data. This is an Enterprise edition feature. More background is at TechNet (although this for SQL Server 2012).
One drawback was that the indexes were not updatable, so data couldn’t be added without first dropping the index. For SQL Server 2014, the indexes will now be updatable. Also, clustered columnstore indexes are available.
As a test, I created a table to store requests for tickets for upcoming football games.

create table TicketWaitList (
RecordId int not null identity(1,1),
HomeState char(2) not null,
HomeCity varchar(20) not null,
FavoriteTeam varchar(20) not null,
GameNumber tinyint not null,
TicketQuantity tinyint
)

It tracks the city and state of the customer making the request, along with their favorite team and how many tickets they wanted to purchase.
I ran two queries, one to aggregate all the data, and a second to return the number of requests per state.

Query #1:

select HomeState, HomeCity, FavoriteTeam, GameNumber, sum(TicketQuantity) as TotalTickets
from TicketWaitList
group by HomeState, HomeCity, FavoriteTeam, GameNumber;

Query #2:

select HomeState, sum(TicketQuantity) as TotalTickets
from TicketWaitList
group by HomeState;

Each query was run three times, one with a standard rowstore index, and with a columnstore index. The table contains 2.7 million records.

create nonclustered index IX_TicketWaitList
on TicketWaitList (HomeState, HomeCity, FavoriteTeam, GameNumber);

create clustered columnstore index IX_TicketWaitList_ColumnStore on TicketWaitList;

Adding the rowstore requires all other indexes be dropped. I threw out the first run for each query (the columnstore query took a while to run the first time) to allow the execution plan to be cached.

Results – times in milliseconds
Rowstore:
Query #1: 2170, 2170, 2196
Query #2: 776, 763, 783

ColumnStore:
Query #1: 110, 43, 43
Query #2: 36, 30, 40

As you can see, the columnstore indexes allow for 15-20 times greater speed on these queries.

I’ve posted the entire SQL script at:
TicketWaitList

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: