Learn performance optimization skills from TPCH tests - Q14

 

I   Query Requirement

  The Q14 statement queries how much of a month's revenue comes from promotional parts. It is used to monitor the market reaction brought about by promotion.

  Q14 statement is characterized by simple query with aggregation and join operations.

II  Oracle Implementation

  The query SQL statements written by Oracle are 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 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

351

275

218

176

157

III SPL optimization

  This is a conventional join-and-sum query, and making full use of parallelism is the optimizing method. Because of the large amount of data in the lineitem table, the column storage advantage of the group table is great.

  SPL script is as follows


A

1

=1

2

=now()

3

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

4

=elapse@m(date,1)

5

=file(path+"part.ctx").create().cursor@m(P_PARTKEY,P_TYPE;;A1).fetch().keys@i(P_PARTKEY)

6

=file(path+"lineitem.ctx").create().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=date   &&L_SHIPDATE< A4,L_PARTKEY:A5;A1)

7

=A6.new(if(like(L_PARTKEY.P_TYPE,"PROMO*"),L_EXTENDEDPRICE   * (1 - L_DISCOUNT),0):v1, L_EXTENDEDPRICE * (1 - L_DISCOUNT):v2)

8

=A7.groups(;   sum(v1):s1,sum(v2):s2)

9

=100.00   * A8(1).s1/A8(1).s2

10

=now()

11

=interval@s(A2,A10)

 

  Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

351

275

218

176

157

SPL group table

101

58

34

24

22