"一、 SQL 及分析查询SQL语句如下： select * from ( select c_custkey,c_name, sum(l_extendedprice * (1 - l_disc .."

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

用 TPCH 练习性能优化 Q10

计算＆AI

一、 SQL及分析

查询SQL语句如下：

select * from (
    select
        c_custkey,c_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        c_acctbal,n_name,c_address,c_phone,c_comment
    from
        customer,orders,lineitem,nation
    where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-05-01'
        and o_orderdate < date '1993-05-01' + interval '3' month
        and l_returnflag = 'R'
        and c_nationkey = n_nationkey
    group by
        c_custkey,
        c_name,
        c_acctbal,
        c_phone,
        n_name,
        c_address,
        c_comment
    order by
        revenue desc
) where rownum <=20;

这是多表关联(其中包括主子表orders和lineitem关联)过滤后的分组聚合运算，然后按分组聚合值排序后取前20条记录。

二、 SPL实现

实现原理与Q3相同，group by子句中有7个字段，在SPL中只需对第一个字段分组即可。

	A
1	=now()
2	1993-5-1
3	=elapse@m(A2,3)
4	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3)
5	=file("lineitem.ctx").open().news@r(A4,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R")
6	=A5.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
7	=A6.top(-20;revenue)
8	=file("nation.btx").import@b().keys@i(N_NATIONKEY)
9	=file("customer.ctx").open()
10	=A7.joinx@q(c_custkey,A9:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT)
11	=A10.switch(C_NATIONKEY,A8:N_NATIONKEY)
12	=A11.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,C_NATIONKEY.N_NAME,C_ADDRESS,C_PHONE,C_COMMENT)
13	=A12.sort@z(revenue)
14	=interval@ms(A1,now())

先把orders和lineitem表的连接结果集上的分组汇总运算做完，然后再基于这个结果集继续做外部相关的外键表关联运算。因为最终结果只要取20条记录，可以只针对这20条记录再做剩下的关联计算，所以不必在分组前做，否则计算量会增大。

A7算出来之后，因为customer表对c_custkey有序，用A7.joinx@q从customer表中有序匹配快速地把相关记录取出来，再去做其它join，无须遍历customer表，减少数据读取量。

三、进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，orders中的O_ORDERDATE已在Q3中转换过了。要使用Q1中介绍的字符串整数化方法，lineitem中的L_RETURNFLAG已在Q1中转化过了。还要使用Q2中介绍的维表主键序号化方法，nation中的N_NATIONKEY、customer中的C_CUSTKEY和C_NATIONKEY、orders中的O_CUSTKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制 nation_9.btx、customer_8.ctx、orders_9.ctx、lineitem_9.ctx分别重命名为nation_10.btx、customer_10.ctx、orders_10.ctx、lineitem_10.ctx。

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

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

	A
1	>env(nation, file("nation_10.btx").import@b())
2	>env(customer, file("customer_10.ctx").open().import())
3	>env(l_returnflag,file("l_returnflag.txt").import@si())

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

计算代码：

	A
1	=now()
2	1993-5-1
3	=days@o(elapse@m(A2,3))
4	=days@o(A2)
5	=l_returnflag.pos@b("R")
6	=file("orders_10.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3)
7	=file("lineitem_10.ctx").open().news@r(A6,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG==A5)
8	=A7.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
9	=A8.top(-20;revenue)
10	=A9.new((c=customer(c_custkey)).C_CUSTKEY,c.C_NAME,revenue,c.C_ACCTBAL,nation(c.C_NATIONKEY).N_NAME,c.C_ADDRESS,c.C_PHONE,c.C_COMMENT)
11	=A10.sort@z(revenue)
12	=interval@ms(A1,now())

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	1993-5-1
3	=elapse@m(A2,3)
4	=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3)
5	=file("lineitem.ctx").open().news@r(A4,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R")
6	=A5.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
7	=A6.top(-20;revenue)
8	=file("nation.btx").import@bv().keys@i(N_NATIONKEY)
9	=file("customer.ctx").open()
10	=A7.joinx@q(c_custkey,A9:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT)
11	=A10.join(C_NATIONKEY,A8,N_NAME)
12	=A11.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,N_NAME,C_ADDRESS,C_PHONE,C_COMMENT)
13	=A12.sort@z(revenue)
14	=interval@ms(A1,now())

2. 优化后数据