RiCo技術農場

(圖片來源:http://www.tpc.org/tpch/results/tpch_perf_results.asp)

SQL2016 重大改善
1.支援Unique Constraints
2.支援Primary Key
3.支援Foreign Keys
4.建立較小資料列群組
5.支援RCSI和SI

/*
RiCo 技術農場
https://dotblogs.com.tw/ricochen

SQL2016 Clustered Columnstore Indexes 效能大耀進
SQL2016 Enterprise Edition 比 SQL2014 Enterprise Edition 整體效能快40%
1.比row store提高10倍以上的壓縮率(資料相同壓縮率更棒)
2.比row store提高查詢large data 10倍以上效能(優化CPU使用率、更高擊中率、更多資料可存入記憶體)
3.比row store減少更多I/O (只擷取所需欄位)
重大改善
1.支援Unique Constraints
2.支援Primary Key
3.支援Foreign Keys
4.建立較小資料列群組
5.支援RCSI和SI
*/
--壓縮率比較(和頁面壓縮)
sp_spaceused 'dbo.Movement_CCI'
go
sp_spaceused 'dbo.Movement'
go

--彙總查詢效能比較
set statistics io,time on
--using clustered columnstore indexes
select b.[Calendar Year],c.Customer
,COUNT(a.[Stock Item Key]) as [NumStock]
,SUM(a.Quantity) as [TotalQuantity] 
,COUNT(a.[Supplier Key]) as [NumSupplier]
,avg(a.[lineage key]) as [AvgInvoice]
from dbo.Movement_CCI a 
join Dimension.[Date] b on b.[Date]=a.[Date Key]
join Dimension.Customer c on c.[Customer Key]=a.[Customer Key]
group by b.[Calendar Year],c.Customer

--using clustered indexes
select b.[Calendar Year],c.Customer
,COUNT(a.[Stock Item Key]) as [NumStock]
,SUM(a.Quantity) as [TotalQuantity] 
,COUNT(a.[Supplier Key]) as [NumSupplier]
,avg(a.[lineage key]) as [AvgInvoice]
from dbo.Movement a 
join Dimension.[Date] b on b.[Date]=a.[Date Key]
join Dimension.Customer c on c.[Customer Key]=a.[Customer Key]
group by b.[Calendar Year],c.Customer

--bulkload 測試
truncate table rsa241_CCI
select count(*) from rsa241_CCI

drop table if exists [rsa241_CCI]
CREATE TABLE [dbo].[rsa241_CCI](
	[num] [int] NOT NULL,
	[stime] [nchar](10) NOT NULL,
	[event_code] [nvarchar](10) NULL,
	[event_des] [nvarchar](50) NULL,
	[event_card] [nvarchar](40) NULL,
	[door_name] [nvarchar](30) NULL,
	[device_id] [nvarchar](40) NULL,
	[site_id] [nvarchar](22) NULL,
	[ip] [nvarchar](26) NULL,
	[device_name] [nvarchar](30) NULL,
	[ncuip] [nvarchar](26) NULL,
	[emp_id] [nvarchar](30) NULL,
	[sdate] [nvarchar](20) NULL,
	[emp_dep] [nvarchar](22) NULL,
)
create clustered columnstore index ccidx on [rsa241_CCI]

begin tran
bulk insert rsa241_CCI
from 'd:\sourcefile.csv'
with(CODEPAGE = '65001',FIELDTERMINATOR = ',', DATAFILETYPE = 'char' ,batchsize =10000)
--with(CODEPAGE = '65001',FIELDTERMINATOR = ',', DATAFILETYPE = 'char' ,tablock)
--with(CODEPAGE = '65001',FIELDTERMINATOR = ',', DATAFILETYPE = 'char')
rollback

checkpoint

--show the total size of logs
select sum([Log Record Length])/1024.0/1024.0 
from sys.fn_dblog(null,null) as tlog
where AllocUnitName like N'%rsa241_CCI%'

 

參考

Columnstore Indexes Guide

Columnstore indexes - defragmentation