Ⅰ SQL code and analysis

Below is the SQL query statement:

select
    c_count,
    count(*) as custdist
from (
    select
        c_custkey,
        count(o_orderkey) c_count
    from
        customer left outer join orders on
            c_custkey = o_custkey
            and o_comment not like '%special%accounts%'
    group by
        c_custkey
) c_orders
group by
    c_count
order by
    custdist desc,
    c_count desc;

The query can be simply regarded as two rounds of regular grouping operations. The first round groups records by custkey and counts the orders each customer has placed, and the second round groups records by the number of orders and counts customers corresponding to each number.

Ⅱ SPL solution

There is a left join in the original SQL statement to specifically include customers who never buy anything (the number of orders placed is 0) after the two rounds of grouping operations because they will be omitted during the process.

	A
1	=now()
2	>filter="*special*accounts*"
3	=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))
4	=A3.groups@u(O_CUSTKEY;count(1):c_count)
5	=A4.len()
6	=A4.groups@um(c_count;count(1):custdist)
7	=file("customer.ctx").open().cursor@m().skip()
8	=A6.insert(0,0,A7-A5)
9	=A6.sort@z(custdist,c_count)
10	=interval@ms(A1,now())

A4 performs the first round of grouping operation, and A6 performs the second round. A7 calculates the number of all customers. A8 gets the number of customers who do not place any orders by subtracting the number of customers who have placed orders from A7’s total number, and inserts the corresponding records to A6 for sorting in the next step.

A4 uses groups@u to perform the grouping operation. @u option enables not to sort by the grouping field O_CUSTKEY after grouping.

Ⅲ Further optimization

1. Optimization method

For the SPL groups() function, if the grouping field values are ordinal numbers, we can use @n option to locate records directly without hash computations. This example uses the dimension table primary key numberization method – customer table’s C_CUSTKEY field and orders table’s O_CUSTKEY field have been converted in the previous examples.

2. Code for data conversion

Copy customer_10.ctx and orders_12.ctx, and rename them customer_13.ctx and orders_13.ctx.

3. Code after data conversion

Computing code:

	A
1	=now()
2	>filter="*special*accounts*"
3	=file("customer_13.ctx").open().cursor().skip()
4	=file("orders_13.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))
5	=A4.groups@n0(O_CUSTKEY;count(1):c_count;A3)
6	=A5.len()
7	=A5.groups@um(c_count;count(1):custdist)
8	=A7.insert(0,0,A3-A6)
9	=A7.sort@z(custdist,c_count)
10	=interval@ms(A1,now())

In A5, groups@n0 means locating the corresponding group directly according to O_CUSTKEY value; @0 enables removing empty groups.

Yet, performing grouping based on the table sequence cursor uses a relatively large memory space. The numberization makes it possible to perform the grouping directly on a sequence, which can reduce memory usage and increase performance.

Computing code:

	A	B
1	=now()
2	>filter="*special*accounts*"
3	=file("customer_13.ctx").open().cursor().skip()
4	=file("orders_13.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))
5	fork A4	=A3.(0)
6		=A5.run(B5(O_CUSTKEY)+=1).skip()
7		return B5
8	=transpose(A5).(~.sum()).groups@m(~:c_count;count(1):custdist)
9	=A8.sort@z(custdist,c_count)
10	=interval@ms(A1,now())

A3 gets the number of records in customer table (denote the number as n for the convenience of description).

A5 computes A4’s cursor through segment-based multithreaded processing. B5 defines a sequence having n members for each thread and assigns 0 to each member. B6 loops over the segment of orders cursor corresponding to the current thread and adds 1 to value of the (O_CUSTKEY)^th member in the sequence. Suppose the number of parallel threads is m, we will get m sequences of length n in A5 after the multithreaded processing finishes execution. Each sequence represents the number of orders placed by O_CUSTKEY in a segment.

A8 performs row-to-column transposition on A5, adds up values of all rows to get the total number of orders placed for each O_CUSTKEY in orders table, and uses groups() function to count customers corresponding to each number of orders placed.

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

	A
1	=now()
2	>filter="*special*accounts*"
3	=file("orders.ctx").open().cursor@mv(O_CUSTKEY;!like(O_COMMENT,filter))
4	=A3.groups@uz(O_CUSTKEY;count(1):c_count;10000000)
5	=A4.len()
6	=A4.groups@um(c_count;count(1):custdist).o()
7	=file("customer.ctx").open().cursor().skip()
8	=A6.insert(0,0,A7-A5)
9	=A6.sort@z(custdist,c_count)
10	=interval@ms(A1,now())

In A4, groups()function works with @z option to let all threads share the result set during the multithreaded grouping. The last parameter in the function is the hash space length for grouping comparison. Generally, the value should be set as the number of groups in the grouping result set. In this example, there are 9999818 groups after grouping, so we set the parameter value as 10000000. @z option is used when the grouping result set is rather large (usually the number of groups reaches over 1 million).

In A6, o() function converts a column-wise table sequence to a regular one so that A8 can call the insert method.

2. Optimized data

	A
1	=now()
2	>filter="*special*accounts*"
3	=file("orders_13.ctx").open().cursor@mv(O_CUSTKEY;!like(O_COMMENT,filter))
4	=A3.groups@uz(O_CUSTKEY;count(1):c_count;10000000)
5	=A4.len()
6	=A4.groups@um(c_count;count(1):custdist).o()
7	=file("customer_13.ctx").open().cursor().skip()
8	=A6.insert(0,0,A7-A5)
9	=A6.sort@z(custdist,c_count)
10	=interval@ms(A1,now())

As the column-wise computation hasn’t offer groups@n method yet, the script is the same as that for the original data.

Ⅴ Test result

Unit: Second

	Regular	Column-wise
Before optimization	132	8.4
Numberization with groups@n	80	8.4
Use sequence after numberization	44	-