White Papers

35 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
To check the query execution time improvement in the columnstore index tests shown in Figure 11, the
queries were examined individually by running a single user power test (one user with 22 queries run in the
same order). The power test was run on the baseline tests (without columnstore index) and columnstore
index implemented on the largest partitioned LineItem table. Table 7 shows the comparison of the average
query response times taken by each of the 22 queries.
One user power test query response time comparison Table 7
Queries No. columnstore index
Columnstore index on
LineItem table
Pricing summary report query (Q1)
396.356
38.739
Minimum cost supplier query (Q2)
11.718
6.916
Shipping priority query (Q3)
270.43
152.184
Order priority checking query (Q4) 375.791 102.903
Local supplier volume query (Q5)
310.635
84.571
Forecasting revenue change query (Q6)
239.414
9.767
Volume shipping query (Q7)
421.015
93.029
National market share query (Q8)
542.994
107.376
Product type profit measure query (Q9)
312.575
892.794
Returned item reporting query (Q10)
187.646
74.622
Important stock identification query (Q11)
391.593
172.567
Shipping modes and order priority query (Q12)
150.458
60.468
Customer distribution query (Q13)
521.473
99.774
Promotion effect query (Q14) 114.398 26.164
Top supplier query (Q15)
104.548
18.488
Parts-supplier relationship query (Q16)
34.437
22.467
Small-Quantity-Order Revenue Query (Q17)
3.861
3.622
Large Volume Customer Query (Q18)
280.608
173.477
Discounted revenue query (Q19)
44.126
27.588
Potential part promotion query (Q20)
148.875
77.19
Suppliers who kept orders waiting query (Q21) 949.149 385.989
Global sales opportunity query (Q22)
60.426
28.669
Average Query response time (second)
266
120
From the tests in this section, it is evident that using columnstore index on large tables improved the query
response times of almost all the TPC-H queries. For the one user power test conducted in this section, the
average query response times lowered by 54% when using columnstore index compared to the power test