Learn performance optimization skills from TPCH tests – Q5
I Query Requirement
Q5 queries the statistics of the revenue (calculated by sum (l_extended price * (1-l_discount)) obtained from the suppliers of parts in a certain area, which can be used to determine whether a local distribution center needs to be established in the given area.
Q5 is characterized by multi-table join query operation with grouping, sorting and aggregation.
II Oracle Execution
The query SQL written in Oracle is 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;
/*+ parallel(n) */ is the parallel query syntax of Oracle, 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 except for more foreign key tables involved.
The SPL script is as follows:
A |
|
1 |
=now() |
2 |
1995-1-1 |
3 |
=elapse@y(A2,1) |
4 |
>name="ASIA" |
5 |
=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY) |
6 |
=file("nation.btx").import@b().switch@i(N_REGIONKEY,A5).derive@o().keys@i(N_NATIONKEY) |
7 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY) |
8 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A6).fetch().keys@im(C_CUSTKEY) |
9 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE <A3,O_CUSTKEY:A8) |
10 |
=file("lineitem.ctx").open().news(A9,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7) |
11 |
=A10.select(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY) |
12 |
=A11.groups@u(L_SUPPKEY.S_NATIONKEY.N_NAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
13 |
=A12.sort@z(revenue) |
14 |
return interval@ms(A1,now()) |
The techniques mentioned in the previous query are used extensively here, which are filtering while creating cursor and converting join fields to pointers of foreign key table respectively.
However, 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 composite table |
353 |
177 |
91 |
49 |
34 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese Version