用 TPCH 练习性能优化 Q9

一、 SQL及分析

查询SQL语句如下:

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;

这就是多表关联过滤后的分组聚合运算,其中包括主子表orderslineitem关联以及与双主键外键表partsupp的关联。

二、 SPL实现


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拼到游标上,这里是个双字段主键的外键表,不能再使用在游标建立时匹配并转换的方法。

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法,orders中的O_ORDERDATE已在Q3中转换过了。还要使用Q2中介绍的维表主键序号化方法,nation中的N_NATIONKEYpart中的P_PARTKEYsupplier中的S_SUPPKEYS_NATIONKEYpartsupp中的PS_PARTKEYPS_SUPPKEYlineitem中的L_PARTKEYL_SUPPKEY都已在之前的例子中转换过了

2. 数据转换代码

复制 nation_8.btxpart_8.ctxsupplier_8.ctxpartsupp_2.ctxorders_8.ctxlineitem_8.ctx分别重命名为nation_9.btxpart_9.ctxsupplier_9.ctxpartsupp_9.ctxorders_9.ctxlineitem_9.ctx

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

维表需要预加载,加载代码如下:


A

1

>env(nation, file("nation_9.btx").import@b())

2

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

3

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

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

计算代码:


A

1

=now()

2

>name="chocolate"

3

=part.@m(pos(P_NAME, name))

4

=supplier.@m(S_NATIONKEY)

5

=file("partsupp_9.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A3(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)

6

=file("lineitem_9.ctx").open().cursor@m(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;A3(L_PARTKEY))

7

=file("orders_9.ctx").open().new@r(A6,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)

8

=A7.select(L_PARTKEY=A5.find(L_PARTKEY,L_SUPPKEY).PS_SUPPLYCOST).switch(L_SUPPKEY,A4:#)

9

=nation.len()+1

10

=A8.groups(year(O_ORDERDATE)*A9+L_SUPPKEY:gk;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-L_PARTKEY*L_QUANTITY):profit)

11

=A10.new(nation(gk%A9).N_NAME:nationName,gk\A9:o_year,profit).sort(nationName,-o_year)

12

=interval@ms(A1,now())

A8中用L_PARTKEYL_SUPPKEYA5中按主键查找记录,找到后把PS_SUPPLYCOST赋值给L_PARTKEY,如果没找到对应的记录则此值为null,就会被select函数过滤掉。这个方法与前面介绍的join@i运算性能相当。

A10中使用了Q1介绍的分组键技巧,把按2个字段分组变成按一个字段gk分组。

四、 使用企业版列式计算

1. 原始数据


A

1

=now()

2

>name="chocolate"

3

=file("nation.btx").import@b().keys@i(N_NATIONKEY)

4

=file("part.ctx").open().cursor@mv(P_PARTKEY;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY)

5

=file("supplier.ctx").open().cursor@mv(S_SUPPKEY,S_NATIONKEY).fetch().keys@im(S_SUPPKEY)

6

=file("partsupp.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)

7

=file("lineitem.ctx").open().cursor@mv(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;L_SUPPKEY,A5,S_NATIONKEY)

10

=A9.groups(S_NATIONKEY:nationName,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit)

11

=A10.new(A3.find(nationName).N_NAME:nationName,o_year,profit).sort(nationName,-o_year)

12

=interval@ms(A1,now())

2. 优化后数据

维表需要预加载,加载代码如下:


A

1

>env(nation, file("nation_9.btx").import@bv())

2

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

3

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

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

计算代码:


A

1

=now()

2

>name="chocolate"

3

=part.@m(pos(P_NAME, name))

4

=file("partsupp_9.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A3(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)

5

=file("lineitem_9.ctx").open().cursor@mv(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;A3(L_PARTKEY))

6

=file("orders_9.ctx").open().new@r(A5,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)

7

=A6.join@i(L_PARTKEY:L_SUPPKEY,A4,PS_SUPPLYCOST;L_SUPPKEY,supplier:#,S_NATIONKEY)

8

=nation.len()+1

9

=A7.groups(year(O_ORDERDATE)*A8+S_NATIONKEY:gk;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit)

10

=A9.new(nation(gk%A8).N_NAME:nationName,gk\A8:o_year,profit).sort(nationName,-o_year)

11

=interval@ms(A1,now())

五、 测试结果

单位:秒


常规

列式

优化前

33.5

11.3

优化后

23.8

8.5