用 TPCH 练习性能优化 Q12
一、 SQL及分析
查询SQL语句如下：
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1
else 0 end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1
else 0 end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('TRUCK', 'MAIL')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1995-01-01'
and l_receiptdate < date '1995-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode;
这是主表orders与子表lineitem关联过滤后的分组聚合计算。
二、 SPL实现
这里的orders与lineitem主子表关联优化原理与Q9中类似。
注意到SQL中high_line_count和low_line_count的条件正好相反，其中一个可以被另一个用更简单的计算式计算出来。
| A | |
| 1 | =now() |
| 2 | 1995-1-1 |
| 3 | =elapse@y(A2, 1) |
| 4 | =["MAIL", "TRUCK"] |
| 5 | =file("lineitem.ctx").open().cursor@m(L_ORDERKEY,L_SHIPMODE;L_RECEIPTDATE>=A2 && L_RECEIPTDATE<A3 && A4.contain(L_SHIPMODE) && L_COMMITDATE<L_RECEIPTDATE && L_SHIPDATE<L_COMMITDATE) |
| 6 | =file("orders.ctx").open().new@r(A5,L_SHIPMODE,O_ORDERPRIORITY) |
| 7 | =A6.run(O_ORDERPRIORITY=if(O_ORDERPRIORITY=="1-URGENT" || O_ORDERPRIORITY=="2-HIGH",1,0)) |
| 8 | =A7.groups(L_SHIPMODE;sum(O_ORDERPRIORITY):high_line_count, sum(1-O_ORDERPRIORITY):low_line_count) |
| 9 | =interval@ms(A1,now()) |
三、 进一步优化
1. 优化方法
本例中要使用Q1中介绍的日期整数化方法，lineitem中的L_SHIPDATE、 L_COMMITDATE、L_RECEIPTDATE都已经在前面例子中转换过了。还要使用 Q1中介绍的字符串整数化方法，orders中的O_ORDERPRIORITY已经在前面例子中转换过了，本例还要对lineitem中的L_SHIPMODE进行转换。
2. 数据转换代码
2.1 orders转换
复制 orders_10.ctx重命名为orders_12.ctx。
2.2 lineitem转换
| A | |
| 1 | =file("lineitem.ctx").open().cursor(L_SHIPMODE) |
| 2 | =A1.id(L_SHIPMODE).sort() |
| 3 | =file("l_shipmode.txt").export(A2) |
| 4 | =file("lineitem_10.ctx").open().cursor() |
| 5 | =A4.run(L_SHIPMODE=A2.pos@b(L_SHIPMODE)) |
| 6 | =file("lineitem_12.ctx").create@p(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT) |
| 7 | >A6.append(A5) |
3. 数据转换后的计算代码
计算代码：
| A | |
| 1 | >l_shipmode=file("l_shipmode.txt").import@si() |
| 2 | >o_orderpriority=file("o_orderpriority.txt").import@si() |
| 3 | =now() |
| 4 | 1995-1-1 |
| 5 | =days@o(elapse@y(A4, 1)) |
| 6 | =days@o(A4) |
| 7 | =l_shipmode.(["MAIL", "TRUCK"].contain(~)) |
| 8 | =o_orderpriority.(["1-URGENT","2-HIGH"].contain(~)) |
| 9 | =file("lineitem_12.ctx").open().cursor@m(L_ORDERKEY,L_SHIPMODE;L_RECEIPTDATE>=A6 && L_RECEIPTDATE<A5 && A7(L_SHIPMODE) && L_COMMITDATE<L_RECEIPTDATE && L_SHIPDATE<L_COMMITDATE) |
| 10 | =file("orders_12.ctx").open().new@r(A9,L_SHIPMODE,O_ORDERPRIORITY) |
| 11 | =A10.run(O_ORDERPRIORITY=if(A8(O_ORDERPRIORITY),1,0)) |
| 12 | =A11.groups(L_SHIPMODE;sum(O_ORDERPRIORITY):high_line_count, sum(1-O_ORDERPRIORITY):low_line_count) |
| 13 | =A12.run(L_SHIPMODE=l_shipmode(L_SHIPMODE)) |
| 14 | =interval@ms(A3,now()) |
A13中将整数化的L_SHIPMODE还原为原字符串。
四、 使用企业版列式计算
1. 原始数据
| A | |
| 1 | =now() |
| 2 | 1995-1-1 |
| 3 | =elapse@y(A2, 1) |
| 4 | =["MAIL", "TRUCK"] |
| 5 | =file("lineitem.ctx").open().cursor@mv(L_ORDERKEY,L_SHIPMODE;L_RECEIPTDATE>=A2 && L_RECEIPTDATE<A3 && A4.contain(L_SHIPMODE) && L_COMMITDATE<L_RECEIPTDATE && L_SHIPDATE<L_COMMITDATE) |
| 6 | =file("orders.ctx").open().new@r(A5,L_SHIPMODE,O_ORDERPRIORITY) |
| 7 | =A6.derive@o(if(O_ORDERPRIORITY=="1-URGENT" || O_ORDERPRIORITY=="2-HIGH",1,0):flag) |
| 8 | =A7.groups(L_SHIPMODE;sum(flag):high_line_count, sum(1-flag):low_line_count) |
| 9 | =interval@ms(A1,now()) |
2. 优化后数据
| A | |
| 1 | >l_shipmode=file("l_shipmode.txt").import@si() |
| 2 | >o_orderpriority=file("o_orderpriority.txt").import@si() |
| 3 | =now() |
| 4 | 1995-1-1 |
| 5 | =days@o(elapse@y(A4, 1)) |
| 6 | =days@o(A4) |
| 7 | =l_shipmode.(["MAIL", "TRUCK"].contain(~)) |
| 8 | =o_orderpriority.(["1-URGENT","2-HIGH"].contain(~)) |
| 9 | =file("lineitem_12.ctx").open().cursor@mv(L_ORDERKEY,L_SHIPMODE;L_RECEIPTDATE>=A6 && L_RECEIPTDATE<A5 && A7(L_SHIPMODE) && L_COMMITDATE<L_RECEIPTDATE && L_SHIPDATE<L_COMMITDATE) |
| 10 | =file("orders_12.ctx").open().new@r(A9,L_SHIPMODE,O_ORDERPRIORITY) |
| 11 | =A10.derive@o(if(A8(O_ORDERPRIORITY),1,0):flag) |
| 12 | =A11.groups(L_SHIPMODE;sum(flag):high_line_count,sum(1-flag):low_line_count) |
| 13 | =A12.new(l_shipmode(L_SHIPMODE):L_SHIPMODE,high_line_count,low_line_count) |
| 14 | =interval@ms(A3,now()) |
五、 测试结果
单位：秒
| 常规 | 列式 | |
| 优化前 | 9.9 | 7.4 |
| 优化后 | 6.4 | 3.5 |
英文版