北京
乾学院 9 号会员
1 回帖  •  585 浏览  • 
     ...

SPL 计算性能系列测试：分组累计

 
性能对比(29)

1、测试任务

本任务原型为某大型电器企业ERP系统中的产品库龄统计。

原始数据是如下的一对主子表：

库存表(store)：

字段名

数据类型

字段含义

store

字符串

仓库ID

product

整数

产品ID

total

整数

目前的库存量

入库表(inbound)：

字段名

数据类型

字段含义

store

字符串

仓库ID

product

整数

产品ID

indate

日期

入库日期

num

整数

当次入库数量

现在要计算入库表中指定截止日期前一个时间段（通常是90天）内入库产品的库龄(入库日期距离截止日期的天数):

库龄表(inbound_age)：

字段名

数据类型

字段含义

store

字符串

仓库ID

product

整数

产品ID

indate

日期

入库日期

num

整数

当次入库数量

age

整数

库龄

原始数据以及计算过程有4个细节（以90天时间段为例）：

1、按照入库时间顺序出库，早入库，早出库；
2、近90天入库总量少于实际库存量时，那多出来的产品的库龄都归为“90天以上”；

3、近90天入库总量多于实际库存量时，这90天已经出库了一部分，出库产品要删除掉，不再计算库龄；

4、某天入库的某产品m个，可能出库了一部分n个，那么库龄记录里个数要记为m-n个。

下面通过实际的示例数据展示计算细节：

库存表：

store

product

total

store_id_000001

1

700

store_id_000002

1

1346

store_id_000003

1

850

入库表：

store

product

indate

num

store_id_000001

1

2016-09-07

100

store_id_000001

1

2016-08-13

200

store_id_000003

1

2016-09-23

300

store_id_000003

1

2016-09-15

200

store_id_000003

1

2016-08-25

400

store_id_000003

1

2016-08-07

100

截止日期为2016-9-30，时间段为90天。计算出的库龄表如下：

store

product

indate

num

age

store_id_000001

1

2016-09-07

100

23

store_id_000001

1

2016-08-13

200

48

store_id_000001

1

2016-07-01

400

91

store_id_000003

1

2016-09-23

300

7

store_id_000003

1

2016-09-15

200

15

store_id_000003

1

2016-08-25

350

36

其中有一条indate为2016-07-01的记录，库龄填成91，表示当前库存中库龄在90天以上产品。

看两组数据的计算过程：

1、store=store_id_000001、product=1的产品，库存是700个，有2条入库信息：

2016-09-07入库，库龄是23天，入库100个；当时库存是700 – 100 = 600个

2016-08-13入库，库龄是48天，入库200个；当时库存是600 – 200 = 400个

400个库存是2016-07-01以前入库的，库龄是90天以上。

2、 store=store_id_000003、product=1的产品，库存是850个，有4条入库信息：

2016-09-23入库，库龄是7天，入库300个；当时库存是850 – 300 = 550个

2016-09-15入库，库龄是15天，入库200个；当时库存是550 – 200 = 350个

2016-08-25入库，库龄是36天，入库400个，出库了50个，个数记为库里的350个。

2016-08-07入库的100个，全部出库了，所以也就不再计算库龄。

2、对标技术及测试环境

我们选用了两种关系数据库产品用作对标技术：

1. Oracle常常用于数据库性能测试的标杆，本次任务测试版本是19。

2. Starrocks宣称是更快的OLAP数据库，测试版本 2.5.2

SPL用20230528社区版。

测试环境为单台物理服务器，配置：

2颗Intel3014，主频1.7G，共12核CPU，64G内存，SSD固态硬盘

为考查计算技术对CPU和内存的敏感性，我们在在物理机上安装两套虚拟机环境分别测试：

VM1：8CPU，32G内存

VM2：4CPU，16G内存

Starrocks 至少要安装两个节点 BE 和 FE，将承担计算任务的 BE 安装在虚拟机上，管理节点 FE 安装在物理机上，这样不会影响测试效果。SPL 和 Oracle 都只要在虚拟机下安装就可以了。

3、数据准备

用generateData.splx生成库存表store、入库表inbound。

A

B

C

D

E

1

1000

100

1500

2

200000000

20

60

10

100

3

=date(2015,10,1)

　=date(2016,9,30)

4




5

=create(store,product,total)

6

=file("e:/store.ctx").create@y(#store,#product,total)

7

=create(store,product,indate,num)

8

=file("e:/inbound.ctx").create@y(#store,#product,#indate,num)

9




10

func genOne (store,product)

11

="store_id_"+mid(string(store+1000000),2)

12

>A5.insert(0,B11,product,B1+rand(C1-B1))

13

=B2+rand(C2-B2)

=to(1000).(rand(interval(B3,D3))).id@u().m(to(B13)).sort()

　

14

for B13

=elapse(B3,C13(B14))

=D2+rand(E2-D2)

15

>A7.insert(0,B11,product,C14,D14)

16

>C3 = C3+1

17


18

for 1000000

for A1

=func(genOne,A18,B18)

19

if (C3 >= A2)

>A6.append(A5.cursor()), A8.append(A7.cursor())

20


break A18

21

>A6.append(A5.cursor())

>A5.reset()

22

>A8.append(A7.cursor())

>A7.reset()

23

>A6.close()

>A8.close()


24




25

=file("e:/store.csv").export@c(file("e:/store.ctx").open().cursor())

26

=file("e:/inbound.csv").export@c(file("e:/inbound.ctx").open().cursor())

前3行是对两表数据规模、字段数据范围进行了定义：

A1是产品ID范围；

当前库存量在B1~C1范围内随机，A2为入库表条数；

随机B2~C2个入库日期的记录条数，每次入库数量在D2~E2之间随机产生；

定义起止日期，入库日期从B3~D3的起止日期内随机产生。

A5、A6定义两表数据结构，A6、A8定义两表数据的存储文件(组表格式)；

A10定义一个genOne函数，传入store，product两个主键字段值，生成一条主表库存表的记录，以及这条记录关联的多条子表记录。

第18行对三个主键字段叉乘组合后，调用genOne函数产生每一组主、子表数据。

A25、A26把组表格式(ctx)的两表转存为文本格式(csv)

实际生成了这样规模的测试数据：

库存表506万条，组表格式文件33M，文本格式(csv)123M；

入库表2亿条，1年的时间跨度，组表格式文件604M，文本格式(csv)6.8G；

计算三个月数据 (2016-07-01 ~ 2016-09-30)的库龄，结果集为4500万条；

SPL基于组表文件计算库龄。文本格式数据会导入Oracle、Starrocks，用SQL计算库龄。

4、Oracle SQL测试

SQL

CREATE TABLE inbound_age AS

    WITH t1 AS (

        SELECT d.store,d.product,

            d.indate,

            d.num,

            SUM(d.num) OVER (PARTITION BY d.store,d.product ORDER BY d.indate DESC) AS accu_num,

            ROW_NUMBER()OVER(PARTITION BY d.store,d.product ORDER BY d.indate DESC) rn,

            c.total

        FROM inbound d JOIN store c ON d.store = c.store AND d.product = c.product

        WHERE d.indate>TO_DATE('2016-07-01','YYYY-MM-DD') AND d.indate<=TO_DATE('2016-09-30','YYYY-MM-DD')

    ),

    t11 AS (

        SELECT t1.* FROM

            t1 JOIN (

                SELECT store,product,MAX(rn) max_rn

                FROM t1 WHERE accu_num - num<total GROUP BY store,product

            )t2

            ON t1.store = t2.store AND t1.product = t2.product AND t1.rn = t2.max_rn

    ),

    t_result AS (

        SELECT t.*,

            CASE WHEN over90=0

                THEN ROUND(TO_NUMBER(TO_DATE('2016-09-30','YYYY-MM-DD') - indate))

                ELSE 91 END AS age

        FROM (

            SELECT store,product,indate,

                total-accu_num AS num,1 AS over90

            FROM t11 WHERE total>accu_num

            UNION

            SELECT store,product,indate,

                CASE WHEN total<accu_num

                THEN num-(accu_num-total) ELSE num END AS num,

                0 AS over90

            FROM t1 WHERE accu_num - num<total

        )t ORDER BY store,product,indate DESC

    )

    SELECT /*+ PARALLEL (t_result,8) */ * FROM t_result

用CREATE TABLE AS SELECT……语句把计算结果存入inbound_age表，计算步骤有些复杂，因此使用WITH子语定义了三个中间表。最后的主查询设置了并行数8；VM2是4核CPU，测试时要把并行数改为4。

不存结果

用三个月的入库数据算出库龄后，结果集t_result有4500万条，这个大结果集写回硬盘会比较耗时，为了测出回写大结果集的时间，改写SQL，最后一行只对t_result计数，这样几乎就没有回写硬盘的动作了。

……

SELECT /*+ PARALLEL (t_result,8) */ COUNT(*) cnt FROM t_result

全量数据计算

全量入库记录数据是一整年的，共2亿条，上面测试时选择近3个月的，大概也就是选出5000万条进行计算，算出的结果集条数为4500万。

把计算的日期范围调整成全年(2015-10-01 ~ 2016-09-30)，就能让全部的2亿条都参与计算，可以算出7700万条结果。修改SQL中的日期相关参数：

……

WHERE d.indate>TO_DATE('2015-10-01','YYYY-MM-DD')AND d.indate<=TO_DATE('2016-09-30','YYYY-MM-DD')

……

ELSE 365 END AS age

……

表示库龄为1年及以上。

5、Starrocks SQL测试

SQL

CREATE TABLE inbound_age AS

    WITH t1 AS (

        SELECT d.store,d.product,

            d.indate,

            d.num,

            SUM(d.num) OVER (PARTITION BY d.store,d.product ORDER BY d.indate DESC) AS accu_num,

            ROW_NUMBER()OVER(PARTITION BY d.store,d.product ORDER BY d.indate DESC) rn,

            c.total

        FROM inbound d JOIN store c ON d.store = c.store AND d.product = c.product

        WHERE d.indate> '2016-07-01' AND d.indate<= '2016-09-30'

    ),

    t11 AS (

        SELECT t1.* FROM

            t1 JOIN (

                SELECT store,product,MAX(rn) max_rn

                FROM t1 WHERE accu_num - num<total GROUP BY store,product

            )t2

            ON t1.store = t2.store AND t1.product = t2.product AND t1.rn = t2.max_rn

    ),

    t_result AS (

        SELECT t.*,

            CASE WHEN over90=0

                THEN DATEDIFF('2016-09-30', indate)

                ELSE 91 END AS age

        FROM (

            SELECT store,product, indate,

                total-accu_num AS num,1 AS over90

            FROM t11 WHERE total>accu_num

            UNION

            SELECT store,product,indate,

                CASE WHEN total<accu_num

                THEN num-(accu_num-total) ELSE num END AS num,

                0 AS over90

            FROM t1 WHERE accu_num - num<total

        ) t ORDER BY store,product,indate DESC

    )

    SELECT * FROM t_result

Starrocks不用设置并行数，它会自动控制。

不存结果

和Oracle一样，改写SQL最后一行，改为计数即可：

……

SELECT COUNT(*) cnt FROM t_result

全量数据计算

改变SQL中日期相关的参数：

……

WHERE d.indate> '2015-10-01'AND d.indate<='2016-09-30'

……

ELSE 365 END AS age

……

表示库龄为1年及以上。

6、SPL测试

SPL脚本

A

B

1

=now()

2

=date(2016,7,1)

3

=date(2016,9,30)

4

=file("e:/store.ctx").open().cursor@m(;;8)

5

=file("e:/inbound.ctx").open().cursor(;indate>A2&&indate<=A3;A4)

6

=A5.joinx@m(store:product,A4:store:product,total,0:age, 0:accu)

7

=A6.group@o(store,product).(~.rvs())

8

=A7.(~.run(cum(num;store,product):accu,num-if(accu>total,(accu-total),0):num))

9

=A8.(last=~.m(-1),~=if(last.accu>=last.total,~.select(num>0),~.derive@o().insert(0,last.store,last.product,A2,last.total-last.accu,last.total,0)))

10

=A9.(~.run(interval(indate,A3):age))

11

=A8.conj().new(store,product,indate,num,age)

12

fork A11

=file("e:/age_"/#A12/".btx").export@b(A12)

13

=interval@ms(A1,now())

两表数据按照主键store，product有序存储在组表文件中，A4/A5从组表文件以游标方式加载数据，一边加载，一边进行后续的计算，A4中指定并行度为8(VM2的CPU是4核，并行度改为4)，A5游标用过滤条件只加载待计算的三个月内的数据，并且跟随A4游标同步分段。

A6对两表进行有序归并关联，把主表库存量字段并入入库表。

A7形成计算库龄的小组，并且反转次序，按照入库日期降序；

A8在组内计算出累加量、仍然在库的有效数量；

A9根据累加量与库存量的比对，去掉多余的入库记录、增补90天以上的入库记录(入库日期设置为2016-07-01，计算出库龄为91天，代表90天以上的)；

A10计算出库龄；

A11把各小组的小结果集conj()后new()出最终的大结果集；

第12行用并行代码块fork，把并行的多路游标存出为多个集文件，这些结果集文件仍然会保持有序。12行以前的计算操作都是定义在游标上，并不执行计算，第12行输出结果时才触发一系列的计算。

不存结果

和Oracle、Starrocks一样，不输出明细数据，只计算结果条数，去掉12行的fork代码块(A12/B12)，修改A12，用skip()对结果游标计数：

12

=A11.skip()

全量数据计算

修改A2的起始日期：

2

=date(2015,10,1)

7、测试结果

单位：秒

VM1

VM2

SPL

Oracle

Starrocks

SPL

Oracle

Starrocks

90天数据

存结果

10

181

92

22

252

溢出

只计数

9

92

63

15

167

溢出

全量数据

存结果

30

451

溢出

48

800

溢出

只计数

23

295

溢出

43

601

溢出

8、总结对比

1、 SPL可以采用更高性能的算法，避免关联，而SQL只能使用低效的JOIN运算，性能严重依赖于优化器，对些本例的复杂运算，优化器通常不起作用。同类测试，SPL能比Oracle SQL快10倍以上。

2、 结果集比较大，向硬盘回写结果都明显多耗费一些时间，SPL写出集文件比较快，对性能影响比较小；Oracle和Starrocks性能降低明显。

3、 Starrocks作为专业的OLAP数据库，使用了CPU优化技术，计算性能要好于Oracle，但仍然要使用低效算法，计算性能和SPL相比仍然有相当大的差距。而且，CPU优化技术严重依赖内存计算，在较小内存或较大数据量时无法正常执行。


性能对比(29)