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

原创于 2020-04-28 10:30:32 发布 · 412 阅读

0 ·

CC 4.0 BY-SA版权

集算器专栏收录该内容

263 篇文章

订阅专栏

本文介绍了如何通过SPL优化针对TPCH Q17查询的性能，该查询涉及复杂的聚集和子查询操作。通过将子查询转化为JOIN并利用内存并行计算，显著提高了查询效率。

一、查询要求

Q17语句查询获得比平均供货量的百分之二十还低的小批量订单。对于指定品牌和指定包装类型的零件，决定在一个七年数据库的所有订单中这些订单零件的平均项目数量（过去的和未决的）。如果这些零件中少于平均数20％的订单不再被接纳，那平均一年会损失多少呢？所以此查询可用于计算出如果没有小量订单，平均年收入将损失多少（因为大量商品的货运，将降低管理费用）。

Q17语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。

二、 Oracle执行

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

select /*+ parallel(n) */

sum(l_extendedprice) / 7.0 as avg_yearly

from

lineitem,part

where

p_partkey = l_partkey

and p_brand = 'Brand#33'

and p_container = 'LG DRUM'

and l_quantity < (

select

0.2 * avg(l_quantity)

from

lineitem

where

l_partkey = p_partkey

);

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	363	278	230	173	165

三、 SPL优化

这种在子查询中用等值条件与主表关联的情况，都可以转换成JOIN来计算，从而可以利用JOIN的优化技术。

select /*+ parallel(n) */

sum(l_extendedprice) / 7.0 as avg_yearly

from

lineitem,part,

(select l_partkey lp, 0.2*avg(l_quantity) lq

from lineitem

group by l_partkey

) lpq

where

p_partkey = l_partkey

and p_brand = 'Brand#33'

and p_container = 'LG DRUM'

and l_partkey=lp

and l_quantity < lq

这相当于对lineitem表进行两次外键匹配过滤，其中一个外键表是子查询计算出来的中间表lpq，而因为是内连接，lpq中涉及到的partkey也只在part表过滤后的范围内，因此可以复用part表的过滤。

SPL脚本如下：

	A
1	=1
2	=now()
3	>brand="Brand#33"
4	>container="LG DRUM"
5	=file(path+"part.ctx").create().cursor@m(P_PARTKEY;P_BRAND == brand && P_CONTAINER == container;A1).fetch().keys@i(P_PARTKEY)
6	=file(path+"lineitem.ctx").create()
7	=A6.cursor@m(L_PARTKEY,L_QUANTITY;A5.find(L_PARTKEY);A1)
8	=A7.groups@u(L_PARTKEY;avg(L_QUANTITY)*0.2:avg).keys@i(L_PARTKEY)
9	=A6.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;A5.find(L_PARTKEY),L_PARTKEY:A8;A1)
10	=(A9.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0))))/7.0
11	=now()
12	=interval@s(A2,A11)

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	363	278	230	173	165
SPL组表	185	101	61	39	25

四、进一步优化

在前面的SPL脚本中，A7和A9中对lineitem表按part表外键过滤遍历了两次。如果服务器内存足够，可以把过滤后的结果保存在内存中，无需第二次遍历读数，性能将会提高不少，因为lineitem表数据量很大。

SPL脚本如下：

	A
1	=1
2	=now()
3	>brand="Brand#33"
4	>container="LG DRUM"
5	=file(path+"part.ctx").create().cursor@m(P_PARTKEY;P_BRAND == brand && P_CONTAINER == container;A1).fetch().keys@i(P_PARTKEY)
6	=file(path+"lineitem.ctx").create().cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;A5.find(L_PARTKEY);A1)
7	=A6.fetch()
8	=A7.cursor@m(A1)
9	=A8.groups@u(L_PARTKEY;avg(L_QUANTITY)*0.2:avg)
10	=A7.cursor@m(A1).switch@i(L_PARTKEY,A9:L_PARTKEY)
11	=(A10.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0))))/7.0
12	=now()
13	=interval@s(A2,A12)