Learn performance optimization skills from TPCH tests - Q14
I. Query Requirement
Q14 queries how much of the revenue in a month comes from promotional parts, which is used to monitor the market reaction brought about by promotion.
Q14 is characterized by simple query with aggregation and join operations.
II. Oracle Execution
The query SQL written in Oracle is as follows:
select /*+ parallel(n) */
100.00 * sum(
case when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end)/ sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1995-04-01'
and l_shipdate < date '1995-04-01' + interval '1' month;
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 |
351 |
275 |
218 |
176 |
157 |
III. SPL Optimization
This is a conventional join-and-sum query with taking parallel as the optimization method. A large amount of data in the lineitem table makes the columnar storage of the composite table become a clear advantage.
The SPL script is as follows:
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=elapse@m(A2,1) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_TYPE).fetch().keys@i(P_PARTKEY) |
5 |
=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 &&L_SHIPDATE<A3,L_PARTKEY:A4) |
6 |
=A5.run( L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(pos@h(L_PARTKEY.P_TYPE,"PROMO"),L_EXTENDEDPRICE,0)) |
7 |
=A6.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE)) |
8 |
=100.00*A7(1)/A7(2) |
9 |
return interval@ms(A1,now()) |
Script execution time, Unit: seconds
Number of parallel |
1 |
2 |
4 |
8 |
12 |
Oracle |
351 |
275 |
218 |
176 |
157 |
SPL composite table |
101 |
58 |
34 |
24 |
22 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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