RaqForum 22 No.
1 Reply  •  695 View  • 
     ...

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

 
 •  695 View  •