"一、 SQL 及分析查询SQL语句如下： select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem,part where p .."

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

...

用 TPCH 练习性能优化 Q17

计算＆AI

tpch(25) 性能优化(158)

一、 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多万条数据，此时再测试，优化效果就非常明显。

以下是广告时间

对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟

润乾软件年买断模式

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

嗯，还不太了解好多乾？

猛戳这里

玩转好多乾