Learn performance optimization skills from TPCH tests – Q5

 

Query Requirement

The Q5 statement queries the statistics of the revenue (calculated by sum (l_extended price * (1-l_discount)) obtained through a regional supplier of parts. It can be used to determine whether a local distribution center needs to be established in a given area.

Q5 statement is characterized by multi-table join query operation with grouping, sorting and aggregation operations.

II Oracle Implementation

The query SQL statements written by Oracle are as follows:

select /*+ parallel(n) */

n_name,

sum(l_extendedprice * (1 - l_discount)) as revenue

from

customer,

orders,

lineitem,

supplier,

nation,

region

where

c_custkey = o_custkey

and l_orderkey = o_orderkey

and l_suppkey = s_suppkey

and c_nationkey = s_nationkey

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'ASIA'

and o_orderdate >= date '1995-01-01'

and o_orderdate < date '1995-01-01' + interval '1' year

group by

n_name

order by

revenue desc;

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

672

368

301

224

225

III SPL optimization

The optimization principle is similar to Q3, but involves more foreign key tables.

SPL script is as follows


A

1

=1

2

=now()

3

>date=date("1995-01-01")

4

>name="ASIA"

5

=elapse@y(date,1)

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+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A7;A1).fetch().keys@i(S_SUPPKEY)

9

=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A7;A1).fetch().keys@i(C_CUSTKEY)

10

=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=date && O_ORDERDATE < A5,O_CUSTKEY:A9;A1)

11

=file(path+"lineitem.ctx").create().news(A10,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A8)

12

=A11.select(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY)

13

=A12.groups@u(L_SUPPKEY.S_NATIONKEY.N_NAME;sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)):revenue)

14

=A13.sort(revenue :-1)

15

=now()

16

=interval@s(A2,A15)

The techniques mentioned in the previous article of filtering while creating cursor and converting joined fields into foreign key table pointers are used extensively here.            

Unlike Q3, the field used for grouping in the last is not an ordered L_ORDERKEY, so groups@o can no longer be used.

 

Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

672

368

301

224

225

SPL group table

353

177

91

49

34