用 TPCH 练习性能优化 Q4

一、 SQL及分析

查询SQL语句如下:

select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '1995-10-01'
    and o_orderdate < date '1995-10-01' + interval '3' month
    and exists (
        select * from lineitem
        where l_orderkey = o_orderkey and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority;

它有一个exists子查询,找出子表lineitem中有满足条件l_commitdate < l_receiptdate的记录。exists子句都可以改写成join,将exists改写成join之后是这样:

select
    o_orderpriority,
    count(*) as order_count
from (
    select 
        distinct l_orderkey,o_orderpriority
    from orders
    join lineitem on 
        l_orderkey = o_orderkey 
    where
        o_orderdate >= date '1995-10-01'
        and o_orderdate < date '1995-10-01' + interval '3' month
        and l_commitdate < l_receiptdate
)
group by
    o_orderpriority
order by
    o_orderpriority;

改写之后可以发现,这仍然是个主子表连接的问题,而且这里的连接运算是为了生成针对主表的某种过滤条件,中间结果集实际上是和orders表对应的,所以还要对连接结果做针对orderkey的去重运算。

二、 SPL实现

采用Q3所说的有序归并方法实现连接,同时统计出针对主表的过虑条件。


A

1

=now()

2

1995-10-01

3

=elapse@m(A2,3)

4

=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A2 && O_ORDERDATE<A3)

5

=file("lineitem.ctx").open().news@r(A4,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE)

6

=A5.groups(O_ORDERPRIORITY;count(1):order_count)

7

=interval@ms(A1,now())

A4A5使用了前面解释过的技巧,A5中用news@r对子表lineitemL_ORDERKEY分组,过滤掉不满足L_COMMITDATE<L_RECEIPTDATE条件的组,每组保留一条记录

A6中对O_ORDERPRIORITY分组,统计各组的记录数。

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法,orders中的O_ORDERDATE已在Q3中转换过了,只须对lineitem中的L_COMMITDATEL_RECEIPTDATE进行转化。

还要使用Q1中介绍的字符串整数化方法,对O_ORDERPRIORITY进行转化。

2. 数据转换代码

2.1 orders转换


A

1

=file("orders.ctx").open().cursor(O_ORDERPRIORITY)

2

=A1.id(O_ORDERPRIORITY).sort()

3

=file("o_orderpriority.txt").export(A2)

4

=file("orders_3.ctx").open().cursor()

5

=A4.run(O_ORDERPRIORITY=A2.pos@b(O_ORDERPRIORITY))

6

=file("orders_4.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE, O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT)

7

>A6.append(A5)

2.2 lineitem转换


A

1

=file("lineitem_3.ctx").open().cursor()

2

=A1.run(L_COMMITDATE=days@o(L_COMMITDATE), L_RECEIPTDATE=days@o(L_RECEIPTDATE))

3

=file("lineitem_4.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)

4

>A3.append(A2)

A2 L_COMMITDATEL_RECEIPTDATE转换为小整数

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


A

1

>o_orderpriority=file("o_orderpriority.txt").import@si()

2

=now()

3

1995-10-01

4

=days@o(elapse@m(A3,3))

5

=days@o(A3)

6

=file("orders_4.ctx").open().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A5 && O_ORDERDATE<A4)

7

=file("lineitem_4.ctx").open().news@r(A6,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE)

8

=A7.groups(O_ORDERPRIORITY;count(1):order_count)

9

=A8.run(O_ORDERPRIORITY=o_orderpriority(O_ORDERPRIORITY))

10

=interval@ms(A2,now())

A9 将整数化后的O_ORDERPRIORITY再还原成原始字符串

四、 使用企业版列式计算

1. 原始数据


A

1

=now()

2

1995-10-01

3

=elapse@m(A2,3)

4

=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A2 && O_ORDERDATE<A3)

5

=file("lineitem.ctx").open().news@r(A4,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE)

6

=A5.groups(O_ORDERPRIORITY;count(1):order_count)

7

=interval@ms(A1,now())

A4cursor函数加选项@v,表示使用列式游标读数。A5中使用news函数与A4创建同步游标,它也与同步的A4一样是列式游标。

2. 优化后数据


A

1

>o_orderpriority=file("o_orderpriority.txt").import@si()

2

=now()

3

1995-10-01

4

=days@o(elapse@m(A3,3))

5

=days@o(A3)

6

=file("orders_4.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A5 && O_ORDERDATE<A4)

7

=file("lineitem_4.ctx").open().news@r(A6,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE)

8

=A7.groups(O_ORDERPRIORITY;count(1):order_count)

9

=A8.new(o_orderpriority(O_ORDERPRIORITY):O_ORDERPRIORITY,order_count)

10

=interval@ms(A2,now())

五、 测试结果

单位:秒


常规

列式

优化前

6.5

5.2

优化后

4.7

3.3


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾