性能优化案例课程 TPCH-Q9
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%chocolate%'
) profit
group by
nation,
o_year
order by
nation,
o_year desc;
这是多表关联过滤后的分组聚合运算，其中包括主子表 orders 和 lineitem 关联以及与双主键外键表 partsupp 的关联。
1. 数据存储
大表 orders、lineitem 按照主键 orderkey 有序存储，连接的时候可以有序归并。其他表没有特殊的排序要求，就按照主键有序存储。
这样，就可以继续使用题目 Q3 中的 orders.ctx、lineitem.ctx、customer.ctx。
part、supplier、partsupp 表直接使用题目 Q2 的 part.ctx、supplier.ctx、partsupp.ctx。
region、nation 表直接使用题目 Q2 的 regin.btx，nation.btx。
将这些表复制到本题的主目录中。
2. 一般实现
A |
|
1 |
=now() |
2 |
>name="chocolate" |
3 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY) |
5 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY).fetch().keys@im(S_SUPPKEY) |
6 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY) |
7 |
=file("lineitem.ctx").open().cursor@m(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;A4.find(L_PARTKEY)) |
8 |
=file("orders.ctx").open().new@r(A7,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE) |
9 |
=A8.join@i(L_PARTKEY:L_SUPPKEY,A6,PS_SUPPLYCOST).switch(L_SUPPKEY,A5) |
10 |
=A9.groups(L_SUPPKEY.S_NATIONKEY:nationName,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit) |
11 |
=A10.run(nationName=A3.find(nationName).N_NAME).sort(nationName,-o_year) |
12 |
=interval@ms(A1,now()) |
本例中 orders 无过滤，而 lineitem 有过滤，过滤后的数据行数远小于 orders 的行数，所以在主子表关联时与前面的例子不同。此时在 A7 先构造 lineitem 游标，A8 中用它去过滤主表 orders，从而大大减少读取的数据行数，提高运行效率。
A9 中用 join@i 方法把外键表的字段 PS_SUPPLYCOST 拼到游标上，这里是个双字段主键的外键表，不能再使用在游标建立时匹配并转换的方法。
A10 用 NATIONKEY 代替字符串 NAME 分组，A11 分组结果再转换成字符串。
测试项目 |
执行时间（秒） |
一般实现 |
37 |
3. 数据变换
数据变换要采用前面题目提到的维表主键序号化、日期整数化手段。
region、nation、supplier、part 表，可以直接使用以前的题目 Q2 转换的 region_2.btx，nation_2.btx、supplier_2.ctx、part_2.ctx。
part 表中的枚举型字符串字段 type 要完成数字化，可以用 Q2 的 p_type.btx。
customer 使用 Q5 中的 customer_3.ctx。
orders 表使用 Q3 中的 orders_5.ctx。
lineitem 表使用 Q8 中的 lineitem_4.ctx。
将这些表复制到本题的主目录中。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>name="chocolate" |
|
3 |
=file("nation_2.btx").import@b() |
|
4 |
= file("part_2.ctx").open() |
=A4.cursor@m().skip().(false) |
5 |
=A4.cursor@m(P_PARTKEY;pos(P_NAME, name)).fetch().(B4(P_PARTKEY)=true) |
|
6 |
=file("supplier_2.ctx").open() |
|
7 |
=A6.cursor@m(S_NATIONKEY).fetch().(S_NATIONKEY) |
|
8 |
=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;B4(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY) |
|
9 |
=file("lineitem_4.ctx").open().cursor@m(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;B4(L_PARTKEY)) |
|
10 |
=file("orders_5.ctx").open().new@r(A9,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE) |
|
11 |
=A10.select(L_PARTKEY=A8.find(L_PARTKEY,L_SUPPKEY).PS_SUPPLYCOST).switch(L_SUPPKEY,A7:#) |
|
12 |
=A3.len()+1 |
|
13 |
=A11.groups(year(O_ORDERDATE)*A12+L_SUPPKEY:gk;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-L_PARTKEY*L_QUANTITY):profit) |
|
14 |
=A13.new(A3(gk%A12).N_NAME:nationName,gk\A12:o_year,profit).sort(nationName,-o_year) |
|
15 |
=interval@ms(A1,now()) |
|
A11 中用 L_PARTKEY 和 L_SUPPKEY 在 A5 中按主键查找记录，找到后把 PS_SUPPLYCOST 赋值给 L_PARTKEY，如果没找到对应的记录则此值为 null，就会被 select 函数过滤掉。这个方法与前面介绍的 join@i 运算性能相当。
A13 中使用了 Q1 介绍的分组键技巧，把按 2 个字段分组变成按一个字段 gk 分组。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
37 |
数据变换 |
29 |
4. 列式计算
A |
B |
|
1 |
=now() |
|
2 |
>name="chocolate" |
|
3 |
=file("nation_2.btx").import@b() |
|
4 |
=file("part_2.ctx").open() |
=A4.cursor@m().skip().(false) |
5 |
=A4.cursor@mv(P_PARTKEY;pos(P_NAME, name)).fetch().(B4(P_PARTKEY)=true) |
|
6 |
=file("supplier_2.ctx").open() |
|
7 |
=A6.cursor@m(S_NATIONKEY).fetch() |
|
8 |
=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;B4(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY) |
|
9 |
=file("lineitem_4.ctx").open().cursor@mv(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;B4(L_PARTKEY)) |
|
10 |
=file("orders_5.ctx").open().new@r(A9,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE) |
|
11 |
=A10.join@i(L_PARTKEY:L_SUPPKEY,A8,PS_SUPPLYCOST;L_SUPPKEY,A7:#,S_NATIONKEY) |
|
12 |
=A3.len()+1 |
|
13 |
=A11.groups(year(O_ORDERDATE)*A12+S_NATIONKEY:gk;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit) |
|
14 |
=A13.new(A3(gk%A12).N_NAME:nationName,gk\A12:o_year,profit).sort(nationName,-o_year) |
|
15 |
=interval@ms(A1,now()) |
|
=now() |
||
在列式计算中，要尽量避免 switch 操作，A11 中使用 join 拼接新列，而不再使用 switch 给原有列赋值。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
37 |
数据变换 |
29 |
列式计算 |
12 |
英文版