"一、 SQL 及分析查询SQL语句如下： select nation, o_year, sum(amount) as sum_profit from ( select n_name as n .."

sjr 北京
乾学院 32 号会员
1 回帖 • 710 浏览 • 2 年前

用 TPCH 练习性能优化 Q9

计算＆AI

一、 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;

这就是多表关联过滤后的分组聚合运算，其中包括主子表orders和lineitem关联以及与双主键外键表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_SUPPLYCOSTL_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_NATIONKEY、part中的P_PARTKEY、supplier中的S_SUPPKEY和S_NATIONKEY、partsupp中的PS_PARTKEY和PS_SUPPKEY、lineitem中的L_PARTKEY和L_SUPPKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制 nation_8.btx、part_8.ctx、supplier_8.ctx、partsupp_2.ctx、orders_8.ctx、lineitem_8.ctx分别重命名为nation_9.btx、part_9.ctx、supplier_9.ctx、partsupp_9.ctx、orders_9.ctx、lineitem_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_PARTKEY和L_SUPPKEY在A5中按主键查找记录，找到后把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_SUPPLYCOSTL_QUANTITY):profit)
11	=A10.new(A3.find(nationName).N_NAME:nationName,o_year,profit).sort(nationName,-o_year)
12	=interval@ms(A1,now())

2. 优化后数据