SPL computing performance test series：associate tables and wide table

I. Test task

Fact and dimension tables are common in multidimensional analysis. This test is to associate the fact table with multiple and multi-layer dimension tables and then aggregate by dimensions and, aggregate wide table by dimensions.

Based on TPCH100G dataset, we design the following association operations between large fact table and multiple dimension tables:

1. Associate one fact table with one dimension table, which is a two-table association operation. 2. Associate the primary-sub fact table with four dimension tables, one of which is utilized twice. This is a seven-table association operation. 3. Join the above-mentioned seven-table association into a wide table, and count the wide table.

II. Technologies to be compared

In this test, we only test SPL Enterprise Edition (version 20230528), and select the following two products to make a comparison:

1. Clickhouse 23.3.1, which is said to be the fastest OLAP database in the world

2. Starrocks 3.0.0, which is claimed to be a faster OLAP database

III. Test environment

One physical server with the following configuration:

2 x Intel3014 CPUs, main frequency 1.7G, 12 cores in total

64G memory

SSD (Solid State Drive)

The size of the largest table in TPCH 100G is only about 70G, and is probably smaller than the physical memory of the machine after simple compression. In order to be able to test the computing ability of these products on external storage as well as their sensitivity to memory, we use virtual machines to limit the number of CPUs and the capacity of memory, and design two test environments based on the relatively common cloud VM specifications in the industry:

VM1: 8 CPUs, 32G memory

VM2: 4 CPUs, 16G memory

For Starrocks, at least two nodes, BE and FE, need to be installed. The BE that undertakes computing task is installed on one VM, and the FE that undertakes management task is installed on the physical machine to avoid affecting the test results.

For SPL and Clickhouse, we only need to install them on VM.

For the preparation of test data, refer to: SPL computing performance test series: TPCH.

IV. Test process

1. Two-table association

SQL query statement:

select
    l_year,
    sum(volume) as revenue
from
    (
        Select
            extract(year from l_shipdate) as l_year,
            ( l_extendedprice * (1 - l_discount) ) as volume
        From
            lineitem,
            part
        where
            p_partkey = l_partkey
            and length(p_type)>2
    ) shipping
group by
    l_year
order by
    l_year;

SPL script:

	A
1	=now()
2	=file("part.ctx").open().import@mv(P_TYPE).(len(P_TYPE)>2)
3	=file("lineitem.ctx").open().cursor@mv(L_SHIPDATE,L_EXTENDEDPRICE,L_DISCOUNT;A2(L_PARTKEY))
4	=A3.groups(year(L_SHIPDATE):l_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
5	=interval@ms(A1,now())

2. Seven-table association

SQL query statement:

select
    s_nationname,
    sum(volume) as revenue
from
    (
        Select
            n1.n_name as s_nationname,
            ( l_extendedprice * (1 - l_discount) ) as volume
        From
            supplier,
            lineitem,
            orders,
            customer,
            part,
            nation n1,
            nation n2
        where
            s_suppkey = l_suppkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and c_custkey = o_custkey
            and s_nationkey = n1.n_nationkey
            and c_nationkey = n2.n_nationkey
            and s_comment not like '%xxx%yyy%'
            and o_totalprice>5
            and length(p_type) > 2
            and n1.n_name is not null
            and n2.n_name is not null
            and c_phone is not null
    ) shipping
group by
    s_nationname
order by
    s_nationname;

SPL script:

	A
1	=now()
2	=file("nation.btx").import@bv(N_NAME).(if(N_NAME,N_NAME,null))
3	=file("customer.ctx").open().import@mv(C_NATIONKEY,C_PHONE).(A2(C_NATIONKEY) && C_PHONE)
4	=file("supplier.ctx").open().import@mv(S_NATIONKEY,S_COMMENT).(if(A2(S_NATIONKEY) && !like(S_COMMENT,"*xxx*yyy*"),S_NATIONKEY,null))
5	=file("part.ctx").open().import@mv(P_TYPE).(len(P_TYPE)>2)
6	=file("orders.ctx").open().cursor@mv(O_ORDERKEY;A3(O_CUSTKEY) && O_TOTALPRICE>5)
7	=file("lineitem.ctx").open().news(A6,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT;A5(L_PARTKEY) && A4(L_SUPPKEY))
8	=A7.groups(A2(A4(L_SUPPKEY)):s_nationname;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
9	=interval@ms(A1,now())

3. Wide table

Create a wide table using the associated data of seven original tables of the previous section, then perform the same query on the new wide table and compare the runtime.

SQL statement for creating wide table:

create table widetable ( l_orderkey    int,
    l_linenumber  int,
    l_partkey     int,
    l_suppkey     int,
    l_shipdate    date ,
    l_extendedprice  decimal(15, 2) NOT NULL,
    l_discount    decimal(15, 2) NOT NULL,
    o_totalprice  decimal(15, 2),
    o_custkey     int,
    c_nationname  varchar(25),
    c_phone       varchar(15),
    s_nationname  varchar(25),
    s_comment     varchar(101),
    p_type        varchar(25) );

SQL statement for inserting data into wide table:

select l_orderkey,l_linenumber,l_partkey,l_suppkey,l_shipdate,
    l_extendedprice,l_discount,o_totalprice,o_custkey,
    n2.n_name as c_nationname,c_phone,
    n1.n_name as s_nationname,s_comment,p_type
into widetable
from supplier,lineitem,orders,customer,part,nation n1,nation n2
where
    s_suppkey = l_suppkey
    and p_partkey = l_partkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey

SQL query statement on wide table:

select
    s_nationname,
    sum( l_extendedprice * (1 - l_discount) ) as volume
from widetable
where
    s_comment not like '%xxx%yyy%'
    and o_totalprice>5
    and length(p_type) > 2
    and c_nationname is not null
    and s_nationname is not null
    and c_phone is not null
group by
    s_nationname
order by
    s_nationname;

SPL script for creating a composite table (widetable.ctx) using the same data as wide table:

	A
1	=file("nation.btx").import@b(N_NATIONKEY,N_NAME).keys@i(N_NATIONKEY)
2	=file("customer.ctx").open().import(C_CUSTKEY,C_NATIONKEY,C_PHONE).switch(C_NATIONKEY,A1).keys@im(C_CUSTKEY)
3	=file("supplier.ctx").open().import(S_SUPPKEY,S_NATIONKEY,S_COMMENT).switch(S_NATIONKEY,A1).keys@im(S_SUPPKEY)
4	=file("part.ctx").open().import(P_PARTKEY,P_TYPE)
5	=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_TOTALPRICE,O_CUSTKEY).switch(O_CUSTKEY,A2)
6	=file("lineitem.ctx").open().news(A5,L_ORDERKEY,L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_SHIPDATE,L_EXTENDEDPRICE,L_DISCOUNT,O_TOTALPRICE,O_CUSTKEY)
7	=A6.switch(L_PARTKEY,A4:P_PARTKEY;L_SUPPKEY,A3:S_SUPPKEY)
8	=A7.new(L_ORDERKEY,L_LINENUMBER,L_PARTKEY.P_PARTKEY:L_PARTKEY,L_SUPPKEY.S_SUPPKEY:L_SUPPKEY,L_SHIPDATE,L_EXTENDEDPRICE,L_DISCOUNT,O_TOTALPRICE,O_CUSTKEY.C_CUSTKEY:O_CUSTKEY,O_CUSTKEY.C_NATIONKEY.N_NAME:C_NATIONNAME,O_CUSTKEY.C_PHONE,L_SUPPKEY.S_NATIONKEY.N_NAME:S_NATIONNAME,L_SUPPKEY.S_COMMENT,L_PARTKEY.P_TYPE)
9	=file("widetable.ctx").create(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_SHIPDATE,L_EXTENDEDPRICE,L_DISCOUNT,O_TOTALPRICE,O_CUSTKEY,C_NATIONNAME,C_PHONE,S_NATIONNAME,S_COMMENT,P_TYPE)
10	=A9.append@i(A8)

SPL query script:

	A
1	=now()
2	=file("widetable.ctx").open().cursor@mv(S_NATIONNAME,L_EXTENDEDPRICE,L_DISCOUNT;O_TOTALPRICE>5 && C_NATIONNAME!=null && C_PHONE!= null && S_NATIONNAME!=null && len(P_TYPE)>2 && !like(S_COMMENT,"*xxx*yyy*"))
3	=A2.groups(S_NATIONNAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):volume)
4	=interval@ms(A1,now())

V. Test results

Unit: seconds

	VM1			VM2
	2-table association	7-table association	Wide table	2-table association	7-table association	Wide table
SPL	11.5	30.6	57.7	21.5	55.6	114.2
Starrocks	35.1	73.3	62.1	78.8	152.5	129.9
Clickhouse	89.3	Out-of-memory (OOM)	33.2	204.1	OOM	74.3

VI. Comments on test results

1. Association performance: SPL is the best and significantly better than the other two products; ClickHouse is the worst and far worse than the other two, and OOM occurs;

2. Performance on wide table: ClickHouse is the best and far better than SPL and Starrocks. SPL is slightly better than Starrocks.

3. When associating more tables, the performance of both Starrocks and Clickhouse drops significantly and lowers than that on wide table. Therefore, for the two products, it is often recommended to create wide table for querying.

4. The performance of SPL on association operation is better than its computing performance on wide table, and also better than the computing performance of the other two products on wide table. Therefore, there is no need for SPL to create wide table.

5. From the performance drop degree from VM1 to VM2, we can see that the performance of both Clickhouse and Starrocks decreases by more than 2 times (equivalent to the difference of CPU number), indicating the performance of the two products is very sensitive to memory capacity (the memory capacity of VM2 is halved), in other words, more memory space is occupied when calculating in Clickhouse or Starrocks. In contrast, the performance of SPL decreases by less than 2 times, indicating that less memory space is occupied when calculating in SPL.

6. Conclusions

SPL has good association performance, and can be used for multidimensional analysis without the need to create wide table, which greatly reduces the time to prepare data, reduces hard disk occupation, and improves the real-timeness of analysis. Moreover, SPL takes up less memory space, so the hardware cost is lower.

Clickhouse has better performance on wide table, but it is completely unsuitable for the scenarios involving association. When Clickhouse is used for multidimensional analysis, there is a need to create wide table, which will be cumbersome and take up more hard disk space.

Starrocks does not do a good job on either association or wide table. Although its association performance is better than that of Clickhouse, it is not satisfactory. Therefore, Starrocks is not suitable for both multi-dimensional analysis scenarios.