欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

初次尝试ColumnStore Index

程序员文章站 2024-01-19 21:31:52
...

初次尝试ColumnStore IndexPosted on 1、首先使用非聚集索引 TIME ON SELECT MemberId , ((VExpd) (DD, (DD, FactOrders o WITH ( INDEX (IX_IsBigOrder_O) ) IsBigOrder IsBigOrder MemberId 结果 SQL Server parse and compile time:CPU time ms.SQL Serve

初次尝试ColumnStore Index Posted on

1、首先使用非聚集索引

TIME ON SELECT MemberId , ((VExpd) (DD, (DD, FactOrders o WITH( INDEX(IX_IsBigOrder_O) ) IsBigOrder IsBigOrder MemberId

结果

SQL Server parse and compile time: CPU time ms. SQL Server Execution Times: CPU time ms. SQL Server Execution Times: CPU time ms. (1121211 row(s) affected) . Scan ahead reads 57581, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. . Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. . Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time ms.

2、使用ColumnStore Index

TIME ON SELECT MemberId , ((VExpd) (DD, (DD, FactOrders o WITH( INDEX(IX_CS) ) IsBigOrder IsBigOrder MemberId

结果

SQL Server Execution Times: CPU time ms. SQL Server parse and compile time: CPU time ms. SQL Server Execution Times: CPU time ms. SQL Server Execution Times: CPU time ms. (1121211 row(s) affected) . Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. . Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time ms.

从结果的logical reads上可以看出明显差异,香港空间,CPU time和elapsed time分别2倍和3倍之多。

3、使用ColumnStore Index不能像使用其它非聚集索引那样没有限制,使用ColumnStoreIndex的限制如下:

1.一个表只能有一个ColumnStore Index

2.不能使用过滤索引

3.索引必须是partition-aligned

4.被索引的表变成只读表

5.被索引的列不能是计算列

6.不能使用Include关键字

由于第4点的限制,以前怕麻烦,所以比较抗拒,从这次的尝试来看,香港空间,性能提升还是很大的。

计划运行一阵,跟踪瞧瞧如何。

,网站空间