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

一、     查询要求

 

Q13语句查询获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。

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	=1
2	=now()
3	>filter="*special*accounts*"
4	=file(path+"orders.ctx").create().cursor@m(O_CUSTKEY,O_COMMENT;!like(O_COMMENT,   filter);A1)
5	=A4.groups@u(O_CUSTKEY;count(1):c_count)
6	=A5.len()
7	=A5.cursor@m(A1).groups@u(c_count;count(1):custdist)
8	=file(path+"customer.ctx").create().cursor(C_CUSTKEY).skip()
9	=A7.insert(0,   0, A8-A6)
10	=A7.sort(custdist:-1,   c_count:-1)
11	=now()
12	=interval@s(A2,A11)

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

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	316	175	89	48	37
SPL组表	352	218	131	99	109

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

 

 

四、     进一步优化

 

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

SPL脚本如下：

	A
1	=1
2	=now()
3	>filter="*special*accounts*"
4	=file(path+"orders.ctx").create().cursor@m(O_CUSTKEY,O_COMMENT;!like(O_COMMENT,   filter);A1)
5	=if(A1>3,A1\4,A1)
6	=A4.cursor(A5).groups@n(O_CUSTKEY;count(1):c_count)
7	=A6.len()
8	=A6.cursor@m(A1).groups@u(c_count;count(1):custdist)
9	=file(path+"customer.ctx").create().cursor(C_CUSTKEY).skip()
10	=A8.insert(0,   0, A9-A7)
11	=A8.sort(custdist:-1,   c_count:-1)
12	=now()
13	=interval@s(A2,A12)

 

脚本执行时间，单位：秒

并行数	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反超。