从 TPCH 测试学习性能优化技巧之 Q13

一、     查询要求

Q13 语句根据消费者的订单数量确定消费者的分布,包括过去和现在都没有订单记录的消费者。它计算统计多少消费者没有订单,多少有 1,2,3……个订单, 统计订单数量时要检查订单备注字段不包含指定的 2 个词,以保证订单没有属于一个特殊的订单类别。

Q13语句的特点是:带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

 

二、     Oracle执行

Oracle编写的查询SQL语句如下:

select  /*+ parallel(n) */

         c_count,

         count(*) as custdist

from (

         select

                   c_custkey,

                   count(o_orderkey) c_count

         from

                   customer left outer join orders on

                            c_custkey = o_custkey

                            and o_comment not like '%special%accounts%'

         group by

                   c_custkey

) c_orders

group by

         c_count

order by

         custdist desc,

         c_count desc;

其中/*+ parallel(n) */ Oracle的并行查询语法,n是并行数。

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

316

175

89

48

37

三、     SPL优化

这个查询简单看是对orders做两轮常规分组,第一轮按custkey分组计算出每个顾客的下单数,第二轮再按下单数分组计算出每种下单数各有多少顾客。

注意到原SQL中有个左连接,会将没有下单过的顾客(下单数为0)也统计在内,而上述二轮分组过程则会遗漏掉这些数据,需要事后再补充一下。

 

SPL脚本如下:


A

1

=now()

2

>filter="*special*accounts*"

3

=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))

4

=A3.groups@u(O_CUSTKEY;count(1):c_count)

5

=A4.len()

6

=A4.cursor@m().groups@u(c_count;count(1):custdist)

7

=file("customer.ctx").open().cursor().skip()

8

=A6.insert(0,0,A7-A5)

9

=A6.sort@z(custdist,c_count)

10

return interval@ms(A1,now())

       A4做第一轮分组,A6做第二轮;A7计算所有客户数,减去已下单的就是没下单的客户数,补充到A6上再一起排序。

 

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

316

175

89

48

37

SPL组表

352

218

131

99

109

这个查询SPL没有跑过Oracle,绝大部分时间消耗在第一轮分组(A4)。主要原因在于这里的分组结果集较大,会占用很多内存,而SPL目前还是Java开发,JVM对内存的管理较差,占用内存较多时就会频繁发起垃圾收集动作,消耗很多时间。而C++开发的数据库则没有这个问题。

 

四、      进一步优化

SPLgroups函数在分组时,如果分组字段是序号,那么可以用@n选项直接定位,避免hash计算。本例中第一轮按O_CUSTKEY分组的,而在数据表中O_CUSTKEY都是整数,可以尝试@n选项。但本例中O_CUSTKEY的值较大,也就是分组数多,占用内存大,并行线程多时如果每个线程中都分一个大组,内存将不够用,所以并行数多时,要减少groups@n的并行数。

SPL脚本如下:


A

1

=now()

2

>filter="*special*accounts*"

3

=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))

4

=A3.groups@n(O_CUSTKEY;count(1):c_count).select@m(c_count)

5

=A4.len()

6

=A4.cursor@m().groups@u(c_count;count(1):custdist)

7

=file("customer.ctx").open().cursor().skip()

8

=A6.insert(0,0,A7-A5)

9

=A6.sort@z(custdist,c_count)

10

return interval@ms(A1,now())

 

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

316

175

89

48

37

SPL组表

352

218

131

99

109

SPL组表(@n)

228

137

95

67

57

测试结果可见,效率确实有所提高,在低并行时性能超过了Oracle,高并行时因为内存占用过大导致垃圾收集时间消耗过多而又被Oracle反超。

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾