5.数据查询

　　由于SQL Server数据库默认采取并行查询的方式，因此不单独测试单进程查询，另一方面，SQL Server存在2种不同类型的索引：非聚集索引和聚集索引，这2种索引的结构有较大的差别，为了给出一个使用哪种索引更有利于分析查询的建议，专门分别进行测试。

　　需要说明的是，SQL Server在创建主键后默认对主键列自动进行统计分析。其他列的统计分析可以用存储过程sp_createstats批量进行。原始tpch脚本dss.ri中创建外键的语法不符合SQL Server的格式要求，需要做相应的修改。为了便于便于在执行计划中辨认主键，对主键约束也采取命名方式。

F:\soft\1>osql -E -d tpch -i pkfk.sql -o F:\soft\1\crt_pkfk_comp.log
-- For table REGION
ALTER TABLE REGION ADD CONSTRAINT REGION_PK PRIMARY KEY (R_REGIONKEY);

-- For table NATION
ALTER TABLE NATION ADD CONSTRAINT NATION_PK PRIMARY KEY (N_NATIONKEY);

ALTER TABLE NATION ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) references REGION;

…
-- For table LINEITEM
ALTER TABLE LINEITEM ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references ORDERS;

ALTER TABLE LINEITEM ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;

 　　为了比较不同条件下的查询结果，我们进行了7种组合的查询。分别是：无主键不压缩，聚集主键不压缩，非聚集主键不压缩，无主键行压缩，聚集主键行压缩，聚集主键页压缩，非聚集主键页压缩，每种测试都在更新统计信息sp_updatestats后执行2遍，取第2遍的结果。这样log文件中不包含SQL解释的时间，只留下查询耗费的时间。

　　SQL Server不能在已有的聚集索引主键上进行更改转变为非聚集索引，必须删除后重新创建非聚集索引主键，方法是在PRIMARY KEY 后加关键字nonclustered。例如：

--删除已有的聚集主键
ALTER TABLE REGION DROP CONSTRAINT REGION_PK
go

--创建非聚集索引主键
ALTER TABLE REGION ADD CONSTRAINT REGION_PK PRIMARY KEY nonclustered (R_REGIONKEY);
go

 　　利用前面第一步产生的查询脚本，执行测试的命令行如下,查询结果输出到不同的log文件，以便于比较：注意在更改表存储和索引类型后，更新统计信息。

--创建统计分析
osql -E -d tpch -Q "sp_cretestats" -o stat.log
--原始
osql -E -i mssql_tpch.sql -o mssql_tpch_org.log
osql -E -i mssql_tpch.sql -o mssql_tpch_org2.log
--聚集主键
--创建主键外键
osql -E -i crt_pkrk_ci.sql
--更新统计分析
osql -E -d tpch -Q "sp_updatestats" -o stat.log
--查询
osql -E -i mssql_tpch.sql -o mssql_tpch_org_ci.log
--非聚集主键
…
osql -E -i mssql_tpch.sql -o mssql_tpch_org_ni.log
--行压缩后
…
osql -E -i mssql_tpch.sql -o mssql_tpch_rc.log
--行压缩后聚集主键
…
osql -E -i mssql_tpch.sql -o mssql_tpch_rc_ci.log
--页压缩后聚集主键
…
osql -E -i mssql_tpch.sql -o mssql_tpch_pc_ci.log
--页压缩后非聚集主键
…
osql -E -i mssql_tpch.sql -o mssql_tpch_pc_ni.log

然后用find命令来得到各次运行的时间

find "CPU" mssql_tpch *2.log

D:\soft>find "CPU"tpch_os2.log

---------- F:\DF\TPCH_OS2.LOG
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
CPU 时间 = 341390 毫秒，占用时间 = 5474 毫秒。
CPU 时间 = 16599 毫秒，占用时间 = 803 毫秒。
CPU 时间 = 52569 毫秒，占用时间 = 3066 毫秒。
CPU 时间 = 44187 毫秒，占用时间 = 2589 毫秒。
CPU 时间 = 58638 毫秒，占用时间 = 3781 毫秒。
CPU 时间 = 28173 毫秒，占用时间 = 1914 毫秒。
CPU 时间 = 56942 毫秒，占用时间 = 3199 毫秒。
CPU 时间 = 59749 毫秒，占用时间 = 3836 毫秒。
CPU 时间 = 296116 毫秒，占用时间 = 173939 毫秒。
CPU 时间 = 100656 毫秒，占用时间 = 4230 毫秒。
CPU 时间 = 18879 毫秒，占用时间 = 1127 毫秒。
CPU 时间 = 52138 毫秒，占用时间 = 2660 毫秒。
CPU 时间 = 246964 毫秒，占用时间 = 4120 毫秒。
CPU 时间 = 42353 毫秒，占用时间 = 2477 毫秒。
CPU 时间 = 39563 毫秒，占用时间 = 2577 毫秒。
CPU 时间 = 48124 毫秒，占用时间 = 1346 毫秒。
CPU 时间 = 762275 毫秒，占用时间 = 18992 毫秒。
CPU 时间 = 194923 毫秒，占用时间 = 7677 毫秒。
CPU 时间 = 86102 毫秒，占用时间 = 1565 毫秒。
CPU 时间 = 58629 毫秒，占用时间 = 2913 毫秒。
CPU 时间 = 122043 毫秒，占用时间 = 7432 毫秒。
CPU 时间 = 31362 毫秒，占用时间 = 1575 毫秒。

 　　从上述日志中可以看出，多次运行后SQL解析的时间为0，每个查询都有2个时间，一个是CPU时间，另一个是占用时间，前者约是后者的1倍或多倍，这说明SQL Server是在执行并行查询，因此实际运行时间大为缩短。

　　下面是各组查询测试结果，取实际运行时间。

▲表1 TPC-H scale=10各种压缩和索引数据的测试对比，单位：毫秒

　　从表1可知，如果没有主键约束，SQL Server仍能在大约20分钟运行完22个查询，这说明查询优化器的优化能力不错，而加上主键约束后，在表不压缩的情况下，大约2分钟就能执行完毕，非聚集主键和聚集主键的差距不大，结合前面给出的占用空间数据，自然是聚集主键更有优势。在压缩表的情况下，行压缩比页压缩的查询性能略好，对于同样的页压缩，非聚集主键比聚集主键的查询性能更好，接近非压缩表，这是因为非聚集主键不继承表的压缩属性，它本身是不压缩的，节省了解压的开销。

　　令人惊异的，SQL Server压缩基本上发挥不了提高查询速度的作用，按理说减少了I/O物理读对整体查询应该有帮助的，但事实并非如此，可能跟SQL Server的压缩算法压缩比不高，但解压开销较大有关。

　　从上述数据我们还可以得出以平均表现最好之一的聚集索引数据为基准的时间倍数。

▲表2 TPC-H scale=10各种压缩和索引数据的测试对比，单位：倍(以不压缩聚集索引为1)

　　如上表所示，从合计时间看，聚集主键比不压缩最大提高了33倍，总体大约提高了9倍。非聚集主键的查询性能在不压缩表时和聚集主键总时间差异不大，但从单独的查询观察，大部分不如聚集主键，而个别却超过了聚集主键，而且提高幅度很大，比如第18个查询，看来SQL Server在处理这2种索引的方法上还是有区别的，具体差异原因要通过分析执行计划才能了解。