SQL/MX UPDATE STATISTICS Enhancements

The third and final test data set consisted of six SQL/MP tables containing actual customer data. The

tables contained between 7 and 24 columns and ranged from about 15 million rows to just under 1

billion rows. Row-level sampling was used with various sample sizes.

The results are shown in this table:

Table Row Count Sample

Pre-2.1.1

Error

2.1.1 Error

Table 1 23,074,465 1% row 64.2% 31.7%

Table 1 23,074,465 2% row 77.3% 24.7%

Table 2 22,495,302 1% row 242.8% 51.8%

Table 2 22,495,302 2% row 134.9% 35.3%

Table 3 15,017,615 1% row 138.9% 39.6%

Table 3 15,017,615 2% row 51.8% 30.1%

Table 4 146,370,017 1M row 463.0% 51.9%

Table 4 146,370,017 2M row 252.3% 40.4%

Table 5 413,374,858 1M row 958.1% 97.6%

Table 5 413,374,858 2M row 541.6% 80.3%

Table 6 900,619,606 1M row 2276.6% 95.6%

Table 6 900,619,606 2M row 1138.6% 58.4%

Again, the accuracy is significantly better in Release 2.1.1 than in previous releases.

For the third data set, UPDATE STATISTICS run times were recorded. The tests were run on a three-

node HP NonStop S86000 system, with four CPUs per node.

The results are shown in this table:

Table Row Count Sample Columns

Pre-2.1.1

Time (m)

2.1.1 Time

(m)

Performance

Increase

Table 1 23,074,465 1% row 14 2.1 2.2 29.0%

Table 1 23,074,465 2% row 14 3.1 3.1

Table 2 22,495,302 1% row 24 3.9 4.0 31.4%

Table 2 22,495,302 2% row 24 5.8 5.9

Table 3 15,017,615 1% row 11 1.6 1.7 24.1%

Table 3 15,017,615 2% row 11 2.2 2.3

Table 4 146,370,017 1M row 24 13.3 13.6 24.1%

Table 4 146,370,017 2M row 24 17.9 18.1

Table 5 413,374,858 1M row 11 9.2 9.4 19.6%

Table 5 413,374,858 2M row 11 11.7 11.8

Table 6 900,619,606 1M row 7 19.2 19.2 15.8%

Table 6 900,619,606 2M row 7 22.8 23.0