"一、 SQL 及分析查询SQL语句如下： select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp .."

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

用 TPCH 练习性能优化 Q11

计算＆AI

一、 SQL及分析

查询SQL语句如下：

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
group by
    ps_partkey
having
    sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.000001
        from
            partsupp,
            supplier,
            nation
        where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'CHINA'
    )
order by
    value desc;

我们把下面的子查询看成为视图V：

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
group by
    ps_partkey

则原主体查询等价于

select
    ps_partkey,
    value
from V
where value>0.000001*(select sum(value) from V)

这个V已经是经过分组后的结果集，数量较小，遍历V比直接针对partsupp遍历的计算量要小很多。

二、 SPL实现

	A
1	=now()
2	>name="CHINA"
3	>percent=0.000001
4	=file("nation.btx").import@b().select@1(N_NAME== name).N_NATIONKEY
5	=file("supplier.ctx").open().cursor@m(S_SUPPKEY;S_NATIONKEY==A4).fetch().keys@i(S_SUPPKEY)
6	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5.find(PS_SUPPKEY))
7	=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value)
8	=A7.sum(value)*percent
9	=A7.select(value>A8).sort@z(value)
10	=interval@ms(A1,now())

partsupp表是按主键PS_PARTKEY、PS_SUPPKEY有序的，也就是对PS_PARTKEY有序，这时候针对该字段的分组可以使用有序分组方案，以提高计算V的性能。A7用groups@o执行有序分组，相当于计算出视图V，然后A8、A9对A7进行两次遍历计算出结果。

三、进一步优化

1. 优化方法

本例中要使用Q2中介绍的维表主键序号化方法，supplier中的S_SUPPKEY、partsupp中的PS_SUPPKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制 nation_10.btx、supplier_9.ctx、partsupp_9.ctx分别重命名为nation_11.btx、supplier_11.ctx、partsupp_11.ctx。

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

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

	A
1	>env(nation, file("nation_11.btx").import@b())
2	>env(supplier, file("supplier_11.ctx").open().import())

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

计算代码：

	A
1	=now()
2	>name="CHINA"
3	>percent=0.000001
4	=nation.select@1(N_NAME== name).N_NATIONKEY
5	=supplier.@m(S_NATIONKEY==A4)
6	=file("partsupp_11.ctx").open().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5(PS_SUPPKEY))
7	=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value)
8	=A7.sum(value)*percent
9	=A7.select(value>A8).sort@z(value)
10	=interval@ms(A1,now())

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	>name="CHINA"
3	>percent=0.000001
4	=file("nation.btx").import@b().select@1(N_NAME== name).N_NATIONKEY
5	=file("supplier.ctx").open().cursor@mv(S_SUPPKEY;S_NATIONKEY==A4).fetch().keys@i(S_SUPPKEY)
6	=file("partsupp.ctx").open().cursor@mv(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5.find(PS_SUPPKEY))
7	=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value)
8	=A7.sum(value)*percent
9	=A7.select@v(value>A8).sort@z(value)
10	=interval@ms(A1,now())

2. 优化后数据