北京
乾学院 32 号会员
1 回帖  •  201 浏览  • 
     ...

用 TPCH 练习性能优化 Q14

 
tpch(25) 性能优化(158)

一、 SQL及分析

查询SQL语句如下：

select
    100.00 * sum(
        case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount)
            else 0 end)
        / sum(l_extendedprice * (1 - l_discount) ) as promo_revenue
from
    lineitem,
    part
where
    l_partkey = p_partkey
    and l_shipdate >= date '1995-04-01'
    and l_shipdate < date '1995-04-01' + interval '1' month;

这是2表关联过滤后的聚合运算。

二、 SPL实现

这是一个常规的连接后求和查询，充分利用并行即可。

A

1

=now()

2

1995-4-1

3

=elapse@m(A2,1)

4

=file("part.ctx").open().cursor@m(P_PARTKEY,P_TYPE).fetch().keys@i(P_PARTKEY)

5

=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 &&L_SHIPDATE<A3,L_PARTKEY:A4)

6

=A5.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(pos@h(L_PARTKEY.P_TYPE,"PROMO"),L_EXTENDEDPRICE,0))

7

=A6.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE))

8

=100.00*A7(1)/A7(2)

9

=interval@ms(A1,now())

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，lineitem中的L_SHIPDATE已在Q1中转换过了。还要使用Q2中介绍的维表主键序号化方法，lineitem中的L_PARTKEY都已在之前的例子中转换过了，part中的P_PARTKEY虽然也已经转换过，但是因为其中的P_TYPE也被转化成整数了，而本例中不需要转化P_TYPE，所以重新生成part组表。

2. 数据转换代码

2.1 part转换

A

1

=file("part.ctx").open().cursor().fetch()

2

=A1.run(P_PARTKEY=#)

3

=file("part_14.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT)

4

>A3.append(A2.cursor())

2.2 lineitem转换

复制lineitem_13.ctx重命名为lineitem_14.ctx。

3. 数据转换后的计算代码

维表需要预加载，加载代码如下：

A

1

>env(part, file("part_14.ctx").open().import())

在查询计算之前，需要先运行预加载代码，将小维表读入内存。

计算代码：

A

1

=now()

2

1995-4-1

3

=days@o(elapse@m(A2,1))

4

=days@o(A2)

5

=part.@m(pos@h(P_TYPE,"PROMO"))

6

=file("lineitem_14.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3)

7

=A6.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(A5(L_PARTKEY),L_EXTENDEDPRICE,0))

8

=A7.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE))

9

=100.00*A8(1)/A8(2)

10

=interval@ms(A1,now())

四、 使用企业版列式计算

1. 原始数据

A

1

=now()

2

1995-4-1

3

=elapse@m(A2,1)

4

=file("part.ctx").open().cursor@mv(P_PARTKEY,P_TYPE).fetch().keys@i(P_PARTKEY)

5

=file("lineitem.ctx").open().cursor@mv(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 && L_SHIPDATE<A3).join(L_PARTKEY,A4,P_TYPE)

6

=A5.derive@o(L_EXTENDEDPRICE*(1-L_DISCOUNT):dp,if(pos@h(P_TYPE,"PROMO"),dp,0.0):dp1)

7

=A6.total(sum(dp1),sum(dp))

8

=100.00*A7(1)/A7(2)

9

=interval@ms(A1,now())

2. 优化后数据

维表需要预加载，加载代码如下：

A

1

>env(part, file("part_14.ctx").open().import@v())

在查询计算之前，需要先运行预加载代码，将小维表读入内存。

计算代码：

A

1

=now()

2

1995-4-1

3

=days@o(elapse@m(A2,1))

4

=days@o(A2)

5

=part.(pos@h(p_type(P_TYPE),"PROMO"))

6

=file("lineitem_14.ctx").open().cursor@mv(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3)

7

=A6.derive@o(L_EXTENDEDPRICE*(1-L_DISCOUNT):dp,if(A5(L_PARTKEY),dp,0):dp1)

8

=A7.total(sum(dp1),sum(dp))

9

=100.00*A8(1)/A8(2)

10

=interval@ms(A1,now())

五、 测试结果

单位：秒

常规

列式

优化前

14.2

6.3

优化后

6.6

2.8


以下是广告时间

对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟
润乾软件年买断模式
性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅



嗯，还不太了解好多乾？
猛戳这里
玩转好多乾
tpch(25) 性能优化(158)