Performance optimization case course：TPCH Q20

    select
        s_name,s_address
    from
        supplier,nation
    where
        s_suppkey in (
            select
                ps_suppkey
            from
                partsupp
            where
                ps_partkey in (
                    select
                        p_partkey
                    from
                        part
                    where
                        p_name like 'bisque%'
                )
                and ps_availqty > (
                    select
                        0.5 * sum(l_quantity)
                    from
                        Lineitem
                    where
                        l_partkey = ps_partkey
                        and l_suppkey = ps_suppkey
                        and l_shipdate >= date '1995-01-01'
                        and l_shipdate < date '1995-01-01' + interval '1' year
                )
        )
        and s_nationkey = n_nationkey
        and n_name = 'CHINA'
    order by
        s_name;

The main query is simple, but the nested filtering condition is complex.

The filtering condition is mainly for the S_SUPPKEY field of the supplier table. Since this table is relatively small, it is easy to get S_NAME and S_ADDRESS as long as the SUPPKEY set meeting the condition is calculated. Calculating the SUPPKEY set in steps can make the problem-solving idea clearer.

The original SQL statement is to filter supplier with partsupp. Now we modify it to filter partsupp with supplier, the obtained PS _ SUPPKEY set is the same. The partsupp is relatively large, so regarding supplier and part as dimension tables to filter the fact table partsupp is more conducive to improving performance. Calculation steps:

1. Filter the nation table according to the condition, and then filter the supplier table with the result.

2. Filter the part table according to the condition.

3. Take the results of steps 1 and 2 as foreign key tables to match and filter partsupp table.

These three steps can be viewed as a view v1, expressed in SQL as follows:

    select
    	ps_suppkey
    from
    	partsupp,
    	(
    		select
    			p_partkey
    		from
    			part
    		where
    			p_name like 'bisque%'
    	),
    	(
    		select
    			s_suppkey
    		from
    			supplier,nation
    		where
    			s_nationkey = n_nationkey
    			and n_name = 'CHINA'
    	)
    where
    	ps_partkey=p_partkey
    	and ps_suppkey=s_suppkey

With v1, the original SQL statement can be modified as:

    select
        s_name,s_address
    from
        supplier,
    	(
    	    select
               distinct ps_suppkey
           from
                v1
           where
                ps_availqty > (
                    select
                        0.5 * sum(l_quantity)
                    from
                        lineitem
                    where
                        l_partkey = ps_partkey
                        and l_suppkey = ps_suppkey
                        and l_shipdate >= date '1995-01-01'
                        and l_shipdate < date '1995-01-01' + interval '1' year
                )
    	)  q_suppkey
    where s_suppkey=ps_suppkey；

Observe the subquery q_suppkey, where the calculationsof v1 and lineitem fall under the situation of associating with the primary table according to the equivalence condition. We can use the method explained in previous articles to change the calculations to a JOIN computation between v1 and lineitem. After modification, the SQL statement can be rewritten as:

    select
        s_name,s_address
    from
        supplier,
    	(
    		select
    			distinct ps_suppkey
    		from
    			v1,
    			(select
    				l_partkey,
    				l_suppkey,
    				0.5 * sum(l_quantity) quantity
    			from
    				lineitem
    			where
    				l_shipdate >= date '1995-01-01'
    				and l_shipdate < date '1995-01-01' + interval '1' year
    			group by
    				l_partkey,
    				l_suppkey
    			)
    		where
    			ps_partkey = l_partkey
    			and ps_suppkey = l_suppkey
    			and ps_availqty > quantity
    	)  q_suppkey
    where s_suppkey=ps_suppkey；

Next, we need to continue to modify the subquery q_suppkey. Since the lineitem table is very large, it is necessary to first associate it with v1 to filter out records that do not satisfy the condition, then perform grouping and aggregation, and finally filter out the same L_SUPPKEY set. Rewrite the SQL statement as:

    select
        s_name,s_address
    from
        supplier,
    	(
    		select
    			distinct l_suppkey
    		from
    			(select
    				l_partkey,
    				l_suppkey,
    				ps_availqty,
    				0.5 * sum(l_quantity) quantity
    			from
    				lineitem,v1
    			where
    				ps_partkey = l_partkey
    				and ps_suppkey = l_suppkey
    				and l_shipdate >= date '1995-01-01'
    				and l_shipdate < date '1995-01-01' + interval '1' year
    			group by
    				l_partkey,
    				l_suppkey,
    				ps_availqty
    			)
    		where
    			ps_availqty > quantity
    	) suppkey
    where s_suppkey=l_suppkey;

1. Data storage

There is no special requirement for tables involved in calculation, store them in order by primary key.

Continue to use lineitem.ctx from Q3, part.ctx, supplier.ctx, partsupp.ctx and nation.btx from Q2. Copy them to the main directory of this query.

2. General method

Calculation code:

	A
1	=now()
2	1995-1-1
3	=elapse@y(A2,1)
4	>partname="bisque"
5	>nationname="CHINA"
6	=file("nation.btx").import@b().select@1(N_NAME==nationname).N_NATIONKEY
7	=file("part.ctx").open().cursor@m(P_PARTKEY;pos@h(P_NAME,partname)).fetch().keys@im(P_PARTKEY)
8	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS;S_NATIONKEY==A6).fetch().keys@im(S_SUPPKEY)
9	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY;A7.find(PS_PARTKEY),A8.find(PS_SUPPKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
10	=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_SUPPKEY,L_QUANTITY;L_SHIPDATE>=A2 && L_SHIPDATE<A3)
11	=A10.join@i(L_PARTKEY:L_SUPPKEY,A9:PS_PARTKEY:PS_SUPPKEY,PS_AVAILQTY)
12	=A11.groups@u(L_PARTKEY,L_SUPPKEY;PS_AVAILQTY,sum(L_QUANTITY):quantity)
13	=A12.select(PS_AVAILQTY*2>quantity).id(L_SUPPKEY)
14	=A8.join@im(S_SUPPKEY,A13:~)
15	=A14.new(S_NAME,S_ADDRESS).sort(S_NAME)
16	=interval@ms(A1,now())

A9 is equivalent to calculating the view v1 mentioned above.

A8 retrieves S_NAME and S_ADDRESS while retrieving S_SUPPKEY from the supplier table, which avoids reading them again in A14.

A13 is equivalent to calculating the subquery q_suppkey.

A12 uses the redundant grouping key introduced in previous articles, and PS_AVAILQTY is no longer used as a grouping field, which improves performance.

A13 changes sum(L_QUANTITY)*0.5 to PS_AVAILQTY*2. Both L_QUANTITY and PS_AVAILQTY are integers, which changes decimal multiplication into integer multiplication.

A14 uses join@im, where @i option deletes non-matching records and @m option enables using order-based merge to speed up computation when both S_SUPPKEY and A13 are ordered.

Test result:

Test items	Execution time (seconds)
General method	15

3. Data conversion

For this query, we need to use two optimization methods mentioned in previous articles: dimension table primary key sequence-numberization and date-integer conversion. Copy part_2.ctx, supplier_2.ctx, partsupp_2.ctx and nation_2.btx from Q2 and lineitem_14_4.ctx from Q14 to the main directory of this query.

Calculation code:

	A	B
1	=now()
2	1995-1-1
3	=days@o(elapse@y(A2,1))
4	=days@o(A2)
5	>partname="bisque"
6	>nationname="CHINA"
7	=file("nation_2.btx").import@b().(N_NAME==nationname)
8	=file("part_2.ctx").open()	=A8.cursor().skip().(false)
9	=A8.cursor@m(P_PARTKEY;pos@h(P_NAME,partname)).fetch().run(B8(P_PARTKEY)=true)
10	=file("supplier_2.ctx").open()	=A10.cursor().skip().(null)
11	=A10.cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS;A7(S_NATIONKEY)).fetch()	=A11.run(B10(S_SUPPKEY)=~)
12	=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY;B8(PS_PARTKEY) && B10(PS_SUPPKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
13	=file("lineitem_14_4.ctx").open().cursor@m(L_PARTKEY,L_SUPPKEY,L_QUANTITY;L_SHIPDATE>=A4 && L_SHIPDATE<A3)
14	=A13.join@i(L_PARTKEY:L_SUPPKEY,A12:PS_PARTKEY:PS_SUPPKEY,PS_AVAILQTY)
15	=A14.groups@u(L_PARTKEY,L_SUPPKEY;PS_AVAILQTY,sum(L_QUANTITY):quantity)
16	=A15.select(PS_AVAILQTY*2>quantity).id(L_SUPPKEY)
17	=B10(A16)
18	=A17.new(S_NAME,S_ADDRESS).sort(S_NAME)
19	=interval@ms(A1,now())

A7, B8, B10 and B11 are all aligned sequences. The value of A10 is null or a supplier record, which enables the direct use of the record in A17, without having to read it again.

Test result:

Test items	Execution time (seconds)
General method	15
Data conversion	10

4. Column-wise computing

Calculation code:

	A	B
1	=now()
2	1995-1-1
3	=days@o(elapse@y(A2,1))
4	=days@o(A2)
5	>partname="bisque"
6	>nationname="CHINA"
7	=file("nation_2.btx").import@b().select@1(N_NAME==nationname).N_NATIONKEY
8	=file("part_2.ctx").open()	=A8.cursor@m().skip().(false)
9	=A8.cursor@mv(P_PARTKEY;pos@h(P_NAME,partname)).fetch().run(B8(P_PARTKEY)=true)
10	=file("supplier_2.ctx").open()	=A10.cursor@m().skip().(null)
11	=A10.cursor@mv(S_SUPPKEY,S_NAME,S_ADDRESS;S_NATIONKEY==A7).fetch()	=A11.run(B10(S_SUPPKEY)=~)
12	=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY;B8(PS_PARTKEY) && B10(PS_SUPPKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
13	=file("lineitem_14_4.ctx").open().cursor@mv(L_PARTKEY,L_SUPPKEY,L_QUANTITY;L_SHIPDATE>=A4 && L_SHIPDATE<A3)
14	=A13.join@i(L_PARTKEY:L_SUPPKEY,A12:PS_PARTKEY:PS_SUPPKEY,PS_AVAILQTY)
15	=A14.groups@u(L_PARTKEY,L_SUPPKEY;PS_AVAILQTY,sum(L_QUANTITY):quantity)
16	=A15.select@v(PS_AVAILQTY*2>quantity).id(L_SUPPKEY)
17	=B10(A16)
18	=A17.new(S_NAME,S_ADDRESS).sort(S_NAME)
19	=interval@ms(A1,now())

Test result:

Test items	Execution time (seconds)
General method	15
Data conversion	10
Column-wise computing	6