Database Reference
In-Depth Information
One of the more common issues that increases the response time of parallel queries in row-mode execution
is uneven data distribution. Exchange operators wait for all parallel threads to complete, thus the execution time
depends on the slowest thread. Some threads have more work to do than others when data is unevenly distributed.
Batch-mode execution eliminates such problems. Every thread picks up work items from the shared queue until the
queue is empty.
Columnstore Indexes and Batch-Mode Execution in Action
Let's look at several examples related to columnstore index behavior and performance. Listing 34-3 creates a set
of tables for the database schema shown in Figure
34-1
and populates it with test data. As a final step, it creates a
nonclustered columnstore index on the facts table. Based on the performance of your computer, it could take several
minutes to complete.
Listing 34-3.
Test database creation
create table dbo.DimBranches
(
BranchId int not null primary key,
BranchNumber nvarchar(32) not null,
BranchCity nvarchar(32) not null,
BranchRegion nvarchar(32) not null,
BranchCountry nvarchar(32) not null
);
create table dbo.DimArticles
(
ArticleId int not null primary key,
ArticleCode nvarchar(32) not null,
ArticleCategory nvarchar(32) not null
);
create table dbo.DimDates
(
DateId int not null primary key,
ADate date not null,
ADay tinyint not null,
AMonth tinyint not null,
AnYear tinyint not null,
AQuarter tinyint not null,
ADayOfWeek tinyint not null
);
create table dbo.FactSales
(
DateId int not null
foreign key references dbo.DimDates(DateId),
ArticleId int not null
foreign key references dbo.DimArticles(ArticleId),
BranchId int not null
foreign key references dbo.DimBranches(BranchId),
OrderId int not null,
