用 TPCH 练习性能优化 Q17

一、 SQL及分析

查询SQL语句如下:

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,part
where
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container = 'LG DRUM'
    and l_quantity < (
        select
            0.2 * avg(l_quantity) 
        from
            lineitem
        where
            l_partkey = p_partkey
    );

这种在子查询中用等值条件与主表关联的情况,都可以转换成JOIN来计算,相当于如下SQL

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,part,
    (select 
        l_partkey lp, 
        0.2*avg(l_quantity) lq
    from 
        lineitem
    group by 
        l_partkey
    ) lpq
where
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container = 'LG DRUM'
    and l_partkey = lp
    and l_quantity < lq;

子查询是分组聚合运算,主查询就是多表关联后的聚合运算。

二、 SPL实现


A
1 =now()
2 >brand="Brand#33"
3 >container="LG DRUM"
4 =file("part.ctx").open().cursor@m(P_PARTKEY;P_BRAND==brand && P_CONTAINER==container).fetch().keys@im(P_PARTKEY)
5 =file("lineitem.ctx").open()
6 =A5.cursor@m(L_PARTKEY,L_QUANTITY;A4.find(L_PARTKEY))
7 =A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg).run(avg/=5).keys@im(L_PARTKEY)
8 =A5.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A7)
9 =A8.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0)))/7.0
10 =interval@ms(A1,now())

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的字符串整数化方法,part中的P_BRAND已在Q16中转化,本例还需转化P_CONTAINER。还要使用Q2中介绍的维表主键序号化方法,part中的P_PARTKEY已在Q16中转化,lineitem中的L_PARTKEY已在之前的例子中转换过了

2. 数据转换代码

2.1 lineitem转换

复制lineitem_15.ctx重命名为lineitem_17.ctx

2.2 part转换


A
1 =file("part_16.ctx").open().cursor().fetch()
2 =A1.id(P_CONTAINER).sort()
3 =file("p_container.txt").export(A2)
4 =A1.run(P_CONTAINER=A2.pos@b(P_ CONTAINER))
5 =file("part_17.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT)
6 >A5.append(A4.cursor())

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

维表和字符串字段取值列表需要预加载,加载代码如下:


A
1 >env(p_brand,file("p_brand.txt").import@si())
2 >env(p_container,file("p_container.txt").import@si())
3 >env(part, file("part_17.ctx").open().import())

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

计算代码:


A
1 =now()
2 >brand=p_brand.pos@b("Brand#33")
3 >container=p_container.pos@b("LG DRUM")
4 =part.@m(P_BRAND==brand && P_CONTAINER==container)
5 =file("lineitem_17.ctx").open()
6 =A5.cursor@m(L_PARTKEY,L_QUANTITY;A4(L_PARTKEY))
7 =A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg).run(A4(L_PARTKEY)=avg*0.2)
8 =A5.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A4:#)
9 =A8.total(sum(if(L_QUANTITY<L_PARTKEY,L_EXTENDEDPRICE,0)))/7.0
10 =interval@ms(A1,now())

四、 使用企业版列式计算

1. 原始数据


A
1 =now()
2 >brand="Brand#33"
3 >container="LG DRUM"
4 =file("part.ctx").open().cursor@mv(P_PARTKEY;P_BRAND==brand && P_CONTAINER==container).fetch().keys@im(P_PARTKEY)
5 =file("lineitem.ctx").open()
6 =A5.cursor@mv(L_PARTKEY,L_QUANTITY;A4.find(L_PARTKEY))
7 =A6.groups@u(L_PARTKEY;avg(L_QUANTITY)*0.2:avg).keys@im(L_PARTKEY)
8 =A5.cursor@mv(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A7)
9 =A8.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0)))/7.0
10 =interval@ms(A1,now())

2. 优化后数据

维表和字符串字段取值列表需要预加载,加载代码如下:


A
1 >env(p_brand,file("p_brand.txt").import@si())
2 >env(p_container,file("p_container.txt").import@si())
3 >env(part, file("part_17.ctx").open().import@v())

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

计算代码:


A
1 =now()
2 >brand=p_brand.pos@b("Brand#33")
3 >container=p_container.pos@b("LG DRUM")
4 =part.@m(P_BRAND==brand && P_CONTAINER==container)
5 =file("lineitem_17.ctx").open()
6 =A5.cursor@mv(L_PARTKEY,L_QUANTITY;A4(L_PARTKEY))
7 =A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg)
8 clear A4
9 =part.(null)
10 =A7.run@m(A9(L_PARTKEY)=avg*0.2)
11 =A5.cursor@mv(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A9:#)
12 =A11.total(sum(if(L_QUANTITY<L_PARTKEY,L_EXTENDEDPRICE,0)))/7.0
13 =interval@ms(A1,now())

五、 测试结果

单位:秒


常规 列式
优化前 15.4 7.7
优化后 14.8 6.8

本例lineitem过滤后数据记录只有60多万条,数据量太少,序号化的效果体现不出来,优化效果不明显。如果改变A4中的过滤条件,将&&改成||lineitem过滤后有3800多万条数据,此时再测试,优化效果就非常明显。