"一、 SQL 及分析查询SQL语句如下： select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from cus .."

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

...

用 TPCH 练习性能优化 Q5

计算＆AI

tpch(25) 性能优化(158)

一、 SQL及分析

查询SQL语句如下：

select
    n_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue
from
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and l_suppkey = s_suppkey
    and c_nationkey = s_nationkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    and o_orderdate >= date '1995-01-01'
    and o_orderdate < date '1995-01-01' + interval '1' year
group by
    n_name
order by
    revenue desc;

这就是多表关联过滤后的分组聚合运算，其中包括主子表orders和lineitem关联。

二、 SPL实现

实现原理与Q3类似。

	A
1	=now()
2	1995-1-1
3	=elapse@y(A2,1)
4	>name="ASIA"
5	=file("region.btx").import@b().select(R_NAME==name)
6	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A5:R_REGIONKEY).keys(N_NATIONKEY)
7	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;A6.find(S_NATIONKEY)).fetch().keys@im(S_SUPPKEY)
8	=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NATIONKEY;A6.find(C_NATIONKEY)).fetch().keys@im(C_CUSTKEY)
9	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE < A3,O_CUSTKEY:A8)
10	=file("lineitem.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7)
11	=A10.select(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY)
12	=A11.groups@u(L_SUPPKEY.S_NATIONKEY:N_NAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue).run(N_NAME=A6.find(N_NAME).N_NAME)
14	=interval@ms(A1,now())

这里使用了前面题目中说过的游标建立时过滤、主子表有序关联和将关联字段转换成外键表指针的技巧。

与Q3不大相同的是，最后用于分组的字段不是有序的L_ORDERKEY，所以不能使用group@s。A12中用S_NATIONKEY代替N_NAME进行分组，因为整数比较比字符串比较效率高，分组结束后再把它替换成N_NAME。

三、进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，orders中的O_ORDERDATE已在Q3中转换过了。还要使用Q2中介绍的维表主键序号化方法，region中的R_REGIONKEY、nation中的N_NATIONKEY和N_REGIONKEY、supplier中的S_SUPPKEY和S_NATIONKEY都已在Q2中转化过了，orders中的O_CUSTKEY在Q3中转化过了。这里再对customer中的C_NATIONKEY以及lineitem中的L_SUPPKEY进行转化。

2. 数据转换代码

2.1 region、nation、supplier转换

复制region_2.btx、nation_2.btx、supplier_2.ctx分别重命名为region_5.btx、nation_5.btx、supplier_5.ctx。

2.2 orders转换

复制orders_4.ctx重命名为orders_5.ctx。

2.3 customer转换

	A
1	=file("customer_3.ctx").open().import()
2	=file("nation.btx").import@b()
3	=A1.run(C_NATIONKEY=A2.pselect(N_NATIONKEY==C_NATIONKEY))
4	=file("customer_5.ctx").create(#C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT)
	>A4.append(A3)

2.4 lineitem转换

	A
1	=file("supplier.ctx").open().import(S_SUPPKEY).keys@im(S_SUPPKEY)
2	=file("lineitem_4.ctx").open().cursor()
3	=A2.run(L_SUPPKEY=A1.pfind(L_SUPPKEY))
4	=file("lineitem_5.ctx").create@p(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT)
	>A4.append(A3)

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

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

	A
1	>env(region, file(“region_5.btx”).import@b())
2	>env(nation, file("nation_5.btx").import@b())
3	>env(customer, file("customer_5.ctx").open().import())
4	>env(supplier, file("supplier_5.ctx").open().import())

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

计算代码：

	A
1	=now()
2	1995-1-1
3	=days@o(elapse@y(A2,1))
4	=days@o(A2)
5	>name="ASIA"
6	=nation.(region(N_REGIONKEY).R_NAME==name)
7	=supplier.@m(if(A6(S_NATIONKEY), S_NATIONKEY,null))
8	=customer.@m(if(A6(C_NATIONKEY), C_NATIONKEY,null))
9	=file("orders_5.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3,O_CUSTKEY:A8:#)
10	=file("lineitem_5.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7:#)
11	=A10.select(O_CUSTKEY ==L_SUPPKEY)
12	=A11.groups@u(L_SUPPKEY:N_NAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue).run(N_NAME=nation(N_NAME).N_NAME)
14	=interval@ms(A1,now())

这里使用Q2中介绍的维表主键序号化后的对位序列以及与维表行号关联的技巧。

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	1995-1-1
3	=elapse@y(A2,1)
4	>name="ASIA"
5	=file("region.btx").import@b().select@1(R_NAME==name).R_REGIONKEY
6	=file("nation.btx").import@b().select(N_REGIONKEY==A5).derive@o().keys@i(N_NATIONKEY)
7	=file("supplier.ctx").open().cursor@mv(S_SUPPKEY,S_NATIONKEY;A6.find(S_NATIONKEY)).fetch().keys@im(S_SUPPKEY)
8	=file("customer.ctx").open().cursor@mv(C_CUSTKEY,C_NATIONKEY;A6.find(C_NATIONKEY)).fetch().keys@im(C_CUSTKEY)
9	=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3,O_CUSTKEY:A8)
10	=file("lineitem.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7)
11	=A10.select@v(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY)
12	=A11.groups@u(L_SUPPKEY.S_NATIONKEY;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue).new(A6.find(S_NATIONKEY).N_NAME,revenue)
14	=interval@ms(A1,now())

A11中在对列式游标执行select过滤时，加选项@v使过滤后的游标仍然是列式游标

2. 优化后数据

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

	A
1	>env(region, file(“region_5.btx”).import@bv())
2	>env(nation, file("nation_5.btx").import@bv())
3	>env(customer, file("customer_5.ctx").open().import@v())
4	>env(supplier, file("supplier_5.ctx").open().import@v())

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

计算代码：

	A
1	=now()
2	1995-1-1
3	=days@o(elapse@y(A2,1))
4	=days@o(A2)
5	>name="ASIA"
6	=nation.(region(N_REGIONKEY).R_NAME==name)
7	=supplier.(A6(S_NATIONKEY))
8	=customer.(A6(C_NATIONKEY))
9	=file("orders_5.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3 && A8(O_CUSTKEY))
10	=file("lineitem_5.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;A7(L_SUPPKEY))
11	=A10.derive@o(supplier(L_SUPPKEY).S_NATIONKEY,customer(O_CUSTKEY).C_NATIONKEY).select@v(C_NATIONKEY==S_NATIONKEY)
12	=A11.groups@u(S_NATIONKEY;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue).new(nation(S_NATIONKEY).N_NAME,revenue)
14	=interval@ms(A1,now())

A9中创建游标时用A8(O_CUSTKEY)只过滤数据，而不做switch关联，原因在Q2中介绍过，A10中也是如此。A11中用derive@o新增2列，然后用select@v继续保持列式游标。

五、测试结果

单位：秒

	常规	列式
优化前	23.5	9.8
优化后	11.5	5.7

以下是广告时间

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

润乾软件年买断模式

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

嗯，还不太了解好多乾？

猛戳这里

玩转好多乾