forked from SeemaSP/shoppingjquerycart
-
Notifications
You must be signed in to change notification settings - Fork 0
/
19 - Columstore indexes.sql
137 lines (116 loc) · 3.37 KB
/
19 - Columstore indexes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
-- View Logical Reads for a Query
USE AdventureWorksDW
GO
-- Execute the query once to get data into cache
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fpi.UnitCost),
SUM(fpi.UnitsOut)
FROM dbo.FactProductInventory as fpi
INNER JOIN dbo.DimProduct as p ON
fpi.ProductKey = p.ProductKey
INNER JOIN dbo.DimDate as d ON
fpi.DateKey = d.DateKey
GROUP BY p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
ORDER BY p.EnglishProductName,
d.CalendarYear,
d.WeekNumberOfYear
-- Now execute and log statistics
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fpi.UnitCost),
SUM(fpi.UnitsOut)
FROM dbo.FactProductInventory as fpi
INNER JOIN dbo.DimProduct as p ON
fpi.ProductKey = p.ProductKey
INNER JOIN dbo.DimDate as d ON
fpi.DateKey = d.DateKey
GROUP BY p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
ORDER BY p.EnglishProductName,
d.CalendarYear,
d.WeekNumberOfYear
-- Create a non-clustered columnstore index
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
USE AdventureWorksDW
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_NCS_FactProductInventory]
ON dbo.FactProductInventory
(
ProductKey,
DateKey,
UnitCost,
UnitsIn,
UnitsOut,
UnitsBalance
);
-- Try to insert a row (this will fail)
INSERT INTO dbo.FactProductInventory
VALUES (214, 20101231, '2010-12-31', 9.54, 2, 0, 4);
-- Now execute and log statistics
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fpi.UnitCost),
SUM(fpi.UnitsOut)
FROM dbo.FactProductInventory as fpi
INNER JOIN dbo.DimProduct as p ON
fpi.ProductKey = p.ProductKey
INNER JOIN dbo.DimDate as d ON
fpi.DateKey = d.DateKey
GROUP BY p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
ORDER BY p.EnglishProductName,
d.CalendarYear,
d.WeekNumberOfYear
-- On the Messages tab, note the elapsed time in the last line of the statistics report
-- Drop the non-clustered columnstore index, existing clustered index, and foreign keys
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DROP INDEX [IX_NCS_FactProductInventory] ON [dbo].[FactProductInventory];
GO
ALTER TABLE dbo.FactProductInventory DROP CONSTRAINT PK_FactProductInventory
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimDate];
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimProduct];
GO
-- Create a clustered columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]
ON dbo.FactProductInventory;
-- Insert a row
INSERT INTO dbo.FactProductInventory
VALUES (214, 20101231, '2010-12-31', 9.54, 2, 0, 4);
-- Now execute and log statistics
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fpi.UnitCost),
SUM(fpi.UnitsOut)
FROM dbo.FactProductInventory as fpi
INNER JOIN dbo.DimProduct as p ON
fpi.ProductKey = p.ProductKey
INNER JOIN dbo.DimDate as d ON
fpi.DateKey = d.DateKey
GROUP BY p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
ORDER BY p.EnglishProductName,
d.CalendarYear,
d.WeekNumberOfYear
-- On the Messages tab, note the elapsed time in the last line of the statistics report