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

用 TPCH 练习性能优化 Q15

 
tpch(25) 性能优化(158)

一、 SQL及分析

查询SQL语句如下：

create view revenue (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1995-04-01'
        and l_shipdate < date '1995-04-01' + interval '3' month
    group by
        l_suppkey;

select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier,
    revenue
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            revenue
    )
order by
    s_suppkey;

drop view revenue;

这是先生成视图revenue，与supplier关联后再选出含有最大值的记录。

二、 SPL实现

这次查询分为两个阶段，先计算出视图revenue，然后再找出revenue中total_revenue达到最大值的记录。前者是个常规的分组汇总，利用并行提高性能。SPL提供了A.maxp方法，可以直接返回最大值所在记录。

A

1

=now()

2

1995-4-1

3

=elapse@m(A2,3)

4

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

5

=A4.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue)

6

=A5.maxp@a(total_revenue)

7

=file("supplier.ctx").open()

8

=A6.joinx@q(supplier_no,A7:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE)

9

=interval@ms(A1,now())

A5计算出视图revenue，A6用maxp@a一次遍历即返回total_revenue最大的那些记录，完成这个运算后再去到supplier表中查出其它字段，减少计算量。

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，lineitem中的L_SHIPDATE已在Q1中转换过了。还要使用Q2中介绍的维表主键序号化方法，supplier中的S_SUPPKEY、lineitem中的L_SUPPKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制supplier_11.ctx、lineitem_14.ctx分别重命名为supplier_15.ctx、lineitem_15.ctx。

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

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

A

1

>env(supplier, file("supplier_15.ctx").open().import())

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

计算代码：

A

1

=now()

2

1995-4-1

3

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

4

=days@o(A2)

5

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

6

=A5.groups@n(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue)

7

=A6.maxp@a(total_revenue)

8

=A7.new(supplier_no,total_revenue,(s=supplier(supplier_no)).S_NAME,s.S_ADDRESS,s.S_PHONE)

9

=interval@ms(A1,now())

A6中使用groups@n进行分组，原理同Q13。A7中maxp得到最大值所在记录。

四、 使用企业版列式计算

1. 原始数据

A

1

=now()

2

1995-4-1

3

=elapse@m(A2,3)

4

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

5

=A4.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue)

6

=A5.maxp@a(total_revenue)

7

=file("supplier.ctx").open()

8

=A6.joinx@q(supplier_no,A7:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE)

9

=interval@ms(A1,now())

2. 优化后数据

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

A

1

>env(supplier, file("supplier_15.ctx").open().import@v())

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

计算代码：

A

1

=now()

2

1995-4-1

3

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

4

=days@o(A2)

5

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

6

=A5.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue)

7

=A6.maxp@a(total_revenue)

8

=A7.new(supplier_no,total_revenue,(s=supplier(supplier_no)).S_NAME,s.S_ADDRESS,s.S_PHONE)

9

return interval@ms(A1,now())

五、 测试结果

单位：秒

常规

列式

优化前

19.6

5.6

优化后

10.0

3.7


以下是广告时间

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



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