"一、 SQL 及分析查询SQL语句如下： select p_brand,p_type,p_size, count(distinct ps_suppkey) as supplier_cnt f .."

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

...

用 TPCH 练习性能优化 Q16

计算＆AI

tpch(25) 性能优化(158)

一、 SQL及分析

查询SQL语句如下：

select
    p_brand,p_type,p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,part
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#21'
    and p_type not like 'SMALL%'
    and p_size in (2, 15, 17, 23, 25, 41, 44, 45)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%'
    )
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size;

这就是多表关联过滤后的分组聚合运算。

二、 SPL实现

这个查询是将partsupp用两个外键表part和supplier进行匹配过滤后再分组，分组时将用两个外键表字段。这时候采用前面所述的技巧，先将外键表按条件过滤后，再将主表的关联字段与外键表匹配并把匹配上的关联字段转换成外键表的记录指针，这样匹配不上的记录可直接过滤掉，而且在后面分组运算时即可直接引用外键表字段。

	A
1	=now()
2	>brand="Brand#21"
3	>type="SMALL"
4	>sizes=[2,15,17,23,25,41,44,45]
5	=file("part.ctx").open().cursor@m(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=brand && !pos@h(P_TYPE,type) && sizes.contain@b(P_SIZE)).fetch().keys@im(P_PARTKEY)
6	=file("supplier.ctx").open().cursor@m(S_SUPPKEY;!like(S_COMMENT,"CustomerComplaints*")).fetch().keys@im(S_SUPPKEY)
7	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A5,PS_SUPPKEY:A6)
8	=A7.groups@u(PS_PARTKEY.P_BRAND,PS_PARTKEY.P_TYPE,PS_PARTKEY.P_SIZE;icount(PS_SUPPKEY):supplier_cnt)
9	=A8.sort@m(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE)
10	=interval@ms(A1,now())

A5和A6分别读取并过滤外键表，A7建立游标同时做匹配和过滤。

注意A5中做in判断时使用了contain@b，表示这里将采用二分法。当in判断的集合成员较多时，将成员先排序后使用二分法可以减少比较次数，从而提高性能。

三、进一步优化

1. 优化方法

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

2. 数据转换代码

2.1 supplier、partsupp转换

复制supplier_15.ctx、partsupp_11.ctx分别重命名为supplier_16.ctx、partsupp_16.ctx。

2.2 part转换

	A
1	=file("part.ctx").open().cursor().fetch()
2	=A1.id(P_BRAND).sort()
3	=file("p_brand.txt").export(A2)
4	=A1.run(P_PARTKEY=#, P_BRAND=A2.pos@b(P_BRAND))
5	=file("part_16.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(part, file("part_16.ctx").open().import())
3	>env(supplier, file("supplier_16.ctx").open().import())

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

计算代码：

	A
1	=now()
2	>brand=p_brand.pos@b("Brand#21")
3	>type="SMALL"
4	>sizes=[2,15,17,23,25,41,44,45]
5	=part.@m(if(P_BRAND!=brand && sizes.contain@b(P_SIZE) && !pos@h(p_type(P_TYPE),type),~,null))
6	=supplier.@m(!like(S_COMMENT,"CustomerComplaints*"))
7	=file("partsupp_16.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A5:#,A6(PS_SUPPKEY))
8	=p_brand.len()+1
9	=A7.groups@u(PS_PARTKEY.P_TYPEA846+PS_PARTKEY.P_SIZE*A8+PS_PARTKEY.P_BRAND:gk;icount(PS_SUPPKEY):supplier_cnt)
10	=A9.new@m(p_brand(gk%A8):P_BRAND,p_type(gk\(A846)):P_TYPE,gk%(A846)\A8:P_SIZE,supplier_cnt)
11	=A10.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE)
12	=interval@ms(A1,now())

A5、A6、A7中使用了Q2中介绍的对位序列技巧。A9中使用了Q1介绍的分组键技巧，将三个字段分组转变成按一个字段gk分组，A10中再用gk返算出原来的三个分组字段。

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	>brand="Brand#21"
3	>type="SMALL"
4	>sizes=[2,15,17,23,25,41,44,45].i()
5	=file("part.ctx").open().cursor@mv(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=brand && sizes.contain@b(P_SIZE) && !pos@h(P_TYPE,type)).fetch().keys@im(P_PARTKEY)
6	=file("supplier.ctx").open().cursor@mv(S_SUPPKEY;!like(S_COMMENT,"CustomerComplaints*")).fetch().keys@im(S_SUPPKEY)
7	=file("partsupp.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A5,A6.find(PS_SUPPKEY))
8	=A7.groups@u(PS_PARTKEY.P_BRAND,PS_PARTKEY.P_TYPE,PS_PARTKEY.P_SIZE;icount(PS_SUPPKEY):supplier_cnt)
9	=A8.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE)
10	=interval@ms(A1,now())

2. 优化后数据

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

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

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

计算代码：

	A
1	=now()
2	>brand=p_brand.pos@b("Brand#21")
3	>type="SMALL"
4	>sizes=[2,15,17,23,25,41,44,45].i()
5	=part.@m(P_BRAND!=brand && sizes.contain@b(P_SIZE) && !pos@h(p_type(P_TYPE),type))
6	=supplier.(!like(S_COMMENT,"CustomerComplaints*"))
7	=file("partsupp_16.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY;A6(PS_SUPPKEY) && A5(PS_PARTKEY))
8	=A7.derive@o(part(PS_PARTKEY):p)
9	=p_brand.len()+1
10	=A8.groups@u(p.P_TYPEA946+p.P_SIZE*A9+p.P_BRAND:gk;icount(PS_SUPPKEY):supplier_cnt)
11	=A10.new@m(p_brand(gk%A9):P_BRAND,p_type(gk\(A946)):P_TYPE,gk%(A946)\A9:P_SIZE,supplier_cnt)
12	=A11.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE)
13	=interval@ms(A1,now())

五、测试结果

单位：秒

	常规	列式
优化前	8.7	2.0
优化后	7.2	1.3

以下是广告时间

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

润乾软件年买断模式

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

嗯，还不太了解好多乾？

猛戳这里

玩转好多乾