Learn performance optimization skills from TPCH tests – Q2

 

I、 Query Requirement

The Q2 statement queries the supplier for the minimum cost. In a given area, which supplier can supply the specified parts (parts of a certain type and size) at the lowest price, and the supplier can be chosen to order from.

The characteristics of Q2 statement are: multi-table query operation with sorting, aggregation and sub-query. Query statements do not grammatically limit how many tuples are returned. The TPC-H standard stipulates that only the first 100 rows of query results need to be returned (usually relying on application implementation).

 

II、 Oracle Implementation

The query SQL statements written by Oracle are as follows:

select * from (

select   /*+ parallel(n) */

s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment

from part,supplier,partsupp,nation,region

where

p_partkey = ps_partkey

and s_suppkey = ps_suppkey

and p_size = 25

and p_type like '%COPPER'

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'ASIA'

and ps_supplycost = (

select

min(ps_supplycost)

from

partsupp,

supplier,

nation,

region

where

p_partkey = ps_partkey

and s_suppkey = ps_suppkey

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'ASIA'

)

order by

s_acctbal desc,n_name,s_name,p_partkey

)

where rownum <= 100;

Where /*+ parallel(n) */ is Oracle's parallel query syntax, and n is the parallel number.

Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

56

35

23

16

27

 

III、 SPL optimization

Analyze carefully this SQL, if the sub-query

select

*

from

part,

partsupp,

supplier,

nation,

region

where

p_partkey = ps_partkey

and s_suppkey = ps_suppkey

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'ASIA'

and p_size = 25

and p_type like '%COPPER'

is regarded as a view V, the original query body can be rewritten as:

select   /*+ parallel(n) */

s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment

from V

where

ps_supplycost = (

select

min(ps_supplycost)

from

V V1

where

V.p_partkey = V1.p_partkey

)

In this way, the original query is transformed into a single table query, which is equivalent to finding some records in V, so that the ps_supplycost value of these records is minimized in all records with the same partkey value of the record. The essence of this operation is to aggregate each group after V is grouped according to partkey and calculate the record with the smallest ps_supplycost in each group. However, this aggregation operation is not supported by SQL, so it can only be written as a sub-query (even when converted into a single table operation).

 

If the database optimization engine doesn’t work well, it will lead to the complexity of N*N (N is the number of records of V) if it is traversed strictly according to the method described by this sub-query. Even if the database optimization engine works well, it needs to first calculate the minimum value of ps_supplycost grouped by part key, then form an intermediate result set and index it, and then traverse V again.

A better way to solve this problem is to support the aggregation calculation of returning record itself, which can be completed by one group aggregation. SPL has set and reference data types, and supports aggregation operation to aggregate records where the minimum value is located. This idea can be realized and the overall complexity will be much lower.

The SPL script is as follows:

 


A

1

=1

2

>size=25

3

>type="*COPPER"

4

>name="ASIA"

5

=now()

6

=file(path+"region.ctx").create().cursor().select(R_NAME==name).fetch()

7

=file(path+"nation.ctx").create().cursor().switch@i(N_REGIONKEY, A6:R_REGIONKEY).fetch().keys@i(N_NATIONKEY)

8

=file(path+"part.ctx").create().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && like(P_TYPE,type);A1).fetch().keys@i(P_PARTKEY)

9

=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A7;A1)

10

=A9.fetch().keys@i(S_SUPPKEY)

11

=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A8,PS_SUPPKEY:A10;A1)

12

=A11.groups(PS_PARTKEY;top(1;PS_SUPPLYCOST):rs).conj(rs)

13

=A12.new(PS_SUPPKEY.S_ACCTBAL,PS_SUPPKEY.S_NAME,PS_SUPPKEY.S_NATIONKEY.N_NAME,PS_PARTKEY.P_PARTKEY,PS_PARTKEY.P_MFGR,PS_SUPPKEY.S_ADDRESS,PS_SUPPKEY.S_PHONE,PS_SUPPKEY.S_COMMENT)

14

=A13.sort(S_ACCTBAL:-1,N_NAME,S_NAME,P_PARTKEY).to(100)

15

=now()

16

=interval@s(A5,A15)

What needs to be explained is that SPL is a bit lower than SQL. SPL does not have metadata concept or system-level table concept like SQL. Data access starts directly from the file, so the code for preparing data in SPL is slightly lengthy. In practical application, it can be simplified by using SPL pre-defined whole process variables or virtual table syntax to achieve the effect similar to SQL using data table directly. But this is not the focus of this article, and in order to make it easier for readers to see the original flow of data, the grammar of direct file access is adopted here.

In code, A6-A11 is used to define the cursor of view V. In A12, top function in groups is used to realize grouping while aggregating the record where the minimum value is located (rather than the minimum value itself).

The switch@i function in A7 filters out records that cannot be matched by foreign keys, and converts the matched joined fields into pointers for foreign key table records, so that the fields of foreign key table can be accessed directly in the form of.(period). The idea of SPL regarding JOIN operation is different from that of SQL. If the data can be loaded into memory beforehand, SPL can use pre-association to improve operation performance. However, all the examples in this series assume that the data is fetched from external storage. In this problem, there is no difference in JOIN performance between SPL and SQL, but only in the way of writing. For detailed explanation please refer to the part about JOIN in SPL teaching plan.

In addition, the technique of using filtering conditions in cursor creation mentioned in Q1 is also used in A8. In A9 and A11, this technique is combined with the previous switch@i method (group 2 parameters). When the cursor is set up, the foreign key matching is done. If the foreign key doesn’t match, filter out directly, the other fields are no longer read and the record is no longer generated. If it matches, the joined field is converted into pointer.

Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

56

35

23

16

27

SPL group table

20

14

8

5

4

 

The data of this problem is not large. After several execution, the operating system can cache data into the memory. The column storage here is not the key point, and the advantage it brings can be ignored. The performance advantage is mainly caused by algorithm optimization.            It can also be seen from the table that the parallel effect of this grouping aggregation is also good.