Database Reference
In-Depth Information
As a next step, let's run two similar queries that calculate the total amount of sales for a particular branch and
article category. The queries are shown in Listing 34-15. The first query uses a DimArticle dimension table for
category filtering, while the second query uses an attribute from the facts table.
Listing 34-15. String columns in facts tables: Test Queries
select sum(s.Amount) as [Sales]
from
dbo.FactSales s join dbo.DimBranches b on
s.BranchId = b.BranchId
join dbo.DimArticles a on
s.ArticleId = a.ArticleId
where
b.BranchNumber = N'3' and
a.ArticleCategory = N'Category 4';
select sum(s.Amount) as [Sales]
from
dbo.FactSales s join dbo.DimBranches b on
s.BranchId = b.BranchId
where
b.BranchNumber = N'3' and
s.ArticleCategory = N'Category 4';
The partial execution plan for the first query is shown in Figure 34-21 . As you can see, SQL Server pushes both
predicates on the BranchId and ArticleId columns to the Columnstore Index Scan operator, filtering out unnecessary
rows during a very early stage of the execution. The elapsed and CPU times of the query running in my environment
are 118 and 125 milliseconds respectively.
Figure 34-21. Execution plan for a query that uses a dimensions table to filter the article category
 
Search WWH ::




Custom Search