用 TPCH 练习性能优化 Q6
一、 SQL及分析
查询SQL语句如下：
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1995-01-01'
and l_shipdate < date '1995-01-01' + interval '1' year
and l_discount between 0.05 - 0.01 and 0.05 + 0.01
and l_quantity < 24;
这是一个简单的单表过滤后的聚合运算。
二、 SPL实现
利用并行查询即可。
A |
|
1 |
=now() |
2 |
1995-1-1 |
3 |
=elapse@y(A2,1) |
4 |
=0.05-0.01 |
5 |
=0.05+0.01 |
6 |
>quantity=24 |
7 |
=file("lineitem.ctx").open().cursor@m(L_EXTENDEDPRICE*L_DISCOUNT:dp;L_SHIPDATE>=A2 && L_SHIPDATE<A3 && L_DISCOUNT>=A4 && L_DISCOUNT<=A5 && L_QUANTITY<quantity) |
8 |
=A7.total(sum(dp)) |
9 |
=interval@ms(A1,now()) |
这里也使用了创建游标时即进行数据过滤以减少读取量的技巧。
三、 进一步优化
1. 优化方法
本例中要使用Q1中介绍的日期整数化优化方法，lineitem中的L_SHIPDATE已在Q1中转换过了。
2. 数据转换代码
2.1 lineitem转换
复制lineitem_5.ctx重命名为lineitem_6.ctx。
3. 数据转换后的计算代码
计算代码：
A |
|
1 |
=now() |
2 |
1995-1-1 |
3 |
=days@o(elapse@y(A2,1)) |
4 |
=days@o(A2) |
5 |
=0.05-0.01 |
6 |
=0.05+0.01 |
7 |
>quantity=24 |
8 |
=file("lineitem_6.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3 && L_DISCOUNT>=A5 && L_DISCOUNT<=A6 && L_QUANTITY<quantity) |
9 |
=A8.total(sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
10 |
=interval@ms(A1,now()) |
四、 使用企业版列式计算
1. 原始数据
A |
|
1 |
=now() |
2 |
1995-1-1 |
3 |
=elapse@y(A2,1) |
4 |
=0.05-0.01 |
5 |
=0.05+0.01 |
6 |
>quantity=24 |
7 |
=file("lineitem.ctx").open().cursor@mv(L_EXTENDEDPRICE,L_DISCOUNT; L_SHIPDATE>=A2 && L_SHIPDATE<A3 && L_QUANTITY<quantity && L_DISCOUNT>=A4 && L_DISCOUNT<=A5) |
8 |
=A7.groups(;sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
9 |
=interval@ms(A1,now()) |
2. 优化后数据
A |
|
1 |
=now() |
2 |
1995-1-1 |
3 |
=days@o(elapse@y(A2,1)) |
4 |
=days@o(A2) |
5 |
=0.05-0.01 |
6 |
=0.05+0.01 |
7 |
>quantity=24 |
8 |
=file("lineitem_6.ctx").open().cursor@mv(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3 && L_DISCOUNT>=A5 && L_DISCOUNT<=A6 && L_QUANTITY<quantity) |
9 |
=A8.groups(;sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
10 |
=interval@ms(A1,now()) |
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
6.6 |
5.2 |
优化后 |
5.5 |
3.3 |
英文版