SQL Server导入性能对比（2）——非聚集列存储索导入

接上文 SQL Server导入性能对比（1）——并行导入， 这次使用非聚集列存储索引来测试性能。

环境

  首先说明一下这里使用的还是SQL 2016以上的非聚集列存储索引，也就是可更新的列存储索引，在2012的时候已经出现但是是不可更新的。
  首先先创建测试表，然后对全部列进行索引化。注意这个时候表实际上是堆表，因为没有任何聚集索引在上面。

CREATE TABLE [dbo].[FactOnlineSales_NCCI](
     [OnlineSalesKey] [int] NOT NULL,
     [StoreKey] [int] NOT NULL,
     [ProductKey] [int] NOT NULL,
     [PromotionKey] [int] NOT NULL,
     [CurrencyKey] [int] NOT NULL,
     [CustomerKey] [int] NOT NULL,
     INDEX IX_FactOnlineSales_NCCI Nonclustered Columnstore ([OnlineSalesKey],[StoreKey],[ProductKey],[PromotionKey],[CurrencyKey],[CustomerKey])
);

测试

  这次我们插入400万数据，因为其实太多数据机器顶不住。

set statistics time, io on;
 
insert into [dbo].[FactOnlineSales_NCCI] WITH (TABLOCK)
                        (
                        OnlineSalesKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey
                        ) 
 
select distinct top 4000000 OnlineSalesKey,  store.StoreKey, sales.ProductKey, PromotionKey, CurrencyKey, CustomerKey 
  FROM [dbo].[FactOnlineSales] sales
        inner join dbo.DimProduct prod
                                on sales.ProductKey = prod.ProductKey
        inner join dbo.DimStore store
                                on sales.StoreKey = store.StoreKey
  where prod.ProductSubcategoryKey >= 10
                        and store.StoreManager >= 30
  option (recompile);

  在我的机器上跑了1分32秒。从执行计划中可以看到，除了插入数据到堆表（Table Insert)之外，还需要进行非聚集索引的insert，在没有并行插入的情况下还是开销很大的。

  我们使用下面的命令检查一***意我把数据库的兼容级别分别改为130和120的，也就是SQL 2016和SQL 2014，检查它们的差异。

select *
from sys.column_store_row_groups
where object_schema_name(object_id) + '.' + object_name(object_id) = 'dbo.FactOnlineSales_NCCI'
order by row_group_id asc;

  可以看出下面第一个图是SQL 2014的，有三个行组，并且两个是Closed一个open，意味着是使用了Delta-Store。

  第二个图也就是下面的图，是SQL 2016的，是compressed，意味着使用了列存储索引的特性。

总结

  从这个测试看出几个问题：

1. 数据库结构对性能的影响比较大，上一文即使是1000万数据而这里是400万数据（注意环境是一样的），时间有明显的差异。非聚集列存储索引的堆表插入性能不如聚集列存储索引使用并行导入的性能。
2. 兼容级别是120和130的时候，Delta-Store的使用都不一样，另外为了验证我的想法，我再次提升到140也就是2017，并查看执行计划，这次只有表更新，没有列存储的更新。看上去是越来越好了，而且时间上也快了10几秒。如果你执行前面的DMV可以看到同样使用了列存储即compressed，没有用到Delta-Store。
3. 使用非聚集列存储索引的堆表，在插入这一步，并没有使用并行，不管是哪个兼容级别，可以右键Table Insert运算符查看属性里面的并行，均为False。

  下一文会演示in-memory搭配聚集列存储索引测试性能。