Ⅰ SQL code and analysis

Below is the SQL query statement:

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
group by
    ps_partkey
having
    sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.000001
        from
            partsupp,
            supplier,
            nation
        where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'CHINA'
    )
order by
    value desc;

If we regard the following subquery as a view V,

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
group by
    ps_partkey

The body of the original query statement is equivalent to:

select
    ps_partkey,
    value
from V
where value>0.000001*(select sum(value) from V)

Here view V is a grouping result set that contains a relatively small number of records. Traversing V is much less computation-intensive than traversing partsupp table directly.

Ⅱ SPL solution

The partsupp table is ordered by the composite primary key consisting of PS_PATKEY field and PS_SUPPKEY field. This is equivalent to the table being ordered by PS_PARTKEY, so we can use the order-based grouping strategy to perform grouping operation on this field in order to increase performance of computing view V. A7 uses groups@o to perform the order-based grouping, which amounts to computing view V. Then A8 and A9 traverse A7 twice to get the final result.

Ⅲ Further optimization

1. Optimization method

In this example, we will use the dimension table primary key numberization method explained in Q2 – supplier table’s S_SUPPKEY field and partsupp table’s PS_SUPPKEY field have been converted in the previous examples.

2. Code for data conversion

Copy nation_10.btx, supplier_9.ctx and partsupp_9.ctx, and rename them nation_11.btx, supplier_11.ctx and partsupp_11.ctx respectively.

3. Code after data conversion

First, we need to preload dimension tables. Below is preloading code:

Before performing the query, we need to first run the preloading code to load small dimension tables into memory.

Computing code:

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

2. Optimized data

First, we need to preload dimension tables. Below is preloading code:

Before performing the query, we need to first run the preloading code to load small dimension tables into memory.

Computing code:

Ⅴ Test result

Unit: Second