Learn performance optimization skills from TPCH tests - Q9
I Query Requirement
Q9 is to query the total profit of all parts ordered in each country in one year.
Q9 is characterized by query operations with grouping, sorting, aggregation and sub-query operations. The main query of the sub-query has no other query objects. The sub-query is relatively simple in structure, and is a multi-table join query in itself. LIKE operator is used in sub-queries, which may not be supported by some query optimizers.
II Oracle Execution
The query SQL written in Oracle is as follows:
select /*+ parallel(n) */
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%chocolate%'
) profit
group by
nation,
o_year
order by
nation,
o_year desc;
Where /*+ 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 |
930 |
502 |
331 |
267 |
234 |
III SPL Optimization
The optimization principle of JOIN between orders and lineitem primary-sub tables here is similar to that in Q3.
The SPL script is as follows:
A |
|
1 |
=now() |
2 |
>name="chocolate" |
3 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY, P_NAME;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY) |
5 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A3).fetch().keys@im(S_SUPPKEY) |
6 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@i(PS_PARTKEY,PS_SUPPKEY) |
7 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE) |
8 |
=file("lineitem.ctx").open().news(A7,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE) |
9 |
=A8.join@i(L_PARTKEY:L_SUPPKEY,A6,PS_SUPPLYCOST).switch(L_SUPPKEY,A5) |
10 |
=A9.groups(L_SUPPKEY.S_NATIONKEY.N_NAME:nation,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit) |
11 |
=A10.sort(nation,-o_year) |
12 |
return interval@ms(A1,now()) |
In A9, the field PS_SUPPLYCOST of the foreign key table is joined to the lineitem cursor by the join@i method. This is a foreign key table with two field primary keys where the method of matching and converting while creating cursor can no longer be used.
Script execution time, Unit: seconds
Number of parallel |
1 |
2 |
4 |
8 |
12 |
Oracle |
930 |
502 |
331 |
267 |
234 |
SPL composite table |
691 |
354 |
180 |
95 |
68 |
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