Learn performance optimization skills from TPCH tests – Q3

 

I、 Query Requirement

The Q3 statement queries for orders with incomes in the top 10 that have not yet been shipped. Among the orders that have not yet been shipped before the specified date, the shipping priority of the order with the largest revenue (orders in descending order of revenue) and the potential revenue (potential revenue is the sum of l_extended price* (1-l_discount).

Q3 statement is characterized by three table query operations with grouping, sorting and aggregation operations. Query statements do not grammatically limit how many tuples are returned. The TPC-H standard stipulates that query results only return the first 10 rows (usually depending on application implementation).

 

II、 Oracle Implementation

The query SQL statements written by Oracle are as follows:

 

select * from (

select /*+ parallel(n) */

l_orderkey,

sum(l_extendedprice * (1 - l_discount)) as revenue,

o_orderdate,

o_shippriority

from

customer,

orders,

lineitem

where

c_mktsegment = 'BUILDING'

and c_custkey = o_custkey

and l_orderkey = o_orderkey

and o_orderdate < date '1995-03-15'

and l_shipdate > date '1995-03-15'

group by

l_orderkey,

o_orderdate,

o_shippriority

order by

revenue desc,

o_orderdate

) where rownum<=10;

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

635

349

312

220

222

 

III、 SPL optimization

This is a typical main and sub-table join. JOIN implemented by SQL usually uses HASH algorithm, and there are many comparisons. When the data of the two tables are larger than the memory, segment HASH needs to be carried out and it is difficult to achieve parallelism.

After sorting the primary keys of the main sub-table, we can use the ordered merging algorithm to achieve join. The number of comparisons is much less, and it is easy to parallelize regardless of the size of the data. SPL supports this method.

In addition, there are three fields in the GROUP BY statement of this SQL example: L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY, because these three fields are to be returned in the result set. But in fact, the last two fields can be determined by the first field, as long as the first field is grouped, the other two fields can be calculated according to the first field, and do not need to participate in the grouping. In this way, the HASH calculation and comparison in grouping will be much less.            

However, SQL can't write such a grammar. It can only write all three fields into GROUP BY. SPL can write such a grouping grammar to reduce the amount of computation.

 

SPL script is as follows


A

1

=1

2

>mktsegment="BUILDING"

3

>date=date("1995-03-15")

4

=now()

5

=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY;C_MKTSEGMENT==mktsegment;A1).fetch().keys@i(C_CUSTKEY)

6

=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<date && A5.find(O_CUSTKEY);A1)

7

=file(path+"lineitem.ctx").create().news(A6,L_ORDERKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>date)

8

=A7.groups@o(L_ORDERKEY;sum(L_EXTENDEDPRICE * (1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY)

9

=A8.sort(revenue:-1,O_ORDERDATE).to(10)

10

=now()

11

=interval@s(A4,A10)

When orderly merging of main and subtable is done in A7, because the main table order has been filtered and the primary keys of the main and sub-table are in the same order, the filtered key values in the main table can be skipped when reading the sub-table by the news method, thus reducing the reading amount of the sub-table.            

It should be emphasized that the lineitem must be segmented and stored matching the orders table, that is, in the data preparation stage, lineitem needs to be segmented according to L_ORDERKEY to create the group table, so as to ensure that there is no misalignment in parallel segmenting.

 

The @o option is used in the group function of A8, because we know that the joined result set is ordered to L_ORDERKEY, we can use the ordered grouping method, so that the grouping only needs to be compared with the previous record, instead of hash calculation and comparison as in the conventional grouping, and the computing performance is much better.            

As mentioned earlier, the groups function here needs to group the L_ORDERKEY field, and the other two fields can be taken out directly without any further comparison.

 

A7 also uses the filtering while creating cursor technique described in the previous article. This technique is also used in A6, where A5.find means that records that foreign keys do not match need to be filtered out, rather than converted to pointers.

 

Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

635

349

312

220

222

SPL group table

186

100

50

30

21

It can be seen that the parallel effect of SPL is very good, close to linear speed-up. At the same time, this involves a large amount of data, and column storage can also play a role.