I、 Query Requirement

Q3 queries for unshipped orders in the top 10 of revenue. Query all the unshipped orders before a specified date about the shipping priority (in descending order of revenue) and the potential revenue (potential revenue is the sum of l_extendedprice* (1-l_discount) ) of the order with the largest revenue.

Q3 is characterized by three table query operations with grouping, sorting and aggregation. Query statement does not grammatically limit how many tuples are returned, but according to the TPC-H standard, only the first 10 rows of query results are returned (which usually depends on application).

 

II、 Oracle Execution

The query SQL written in 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; 

/*+ 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	635	349	312	220	222

 

III、 SPL Optimization

This is a typical primary-sub table join. In general, the HASH algorithm is used in SQL to perform JOIN, which needs many comparisons. HASH JOIN needs to segment the data if the data of the two join tables exceed the memory, which will lead to a difficult parallel implementation.

After sorting the primary keys of the primary-sub table, join can be achieved by taking the algorithm for ordered merging, in which comparison is much less. In addition, it is easy to parallelize regardless of the data size, which is supported in SPL.

Furthermore, 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 one is grouped, the other two fields can be calculated from the first field, without the necessity to participate in grouping. In this way, the calculation and comparison of HASH in grouping will be much less.

However, such syntax is not allowed in SQL, so all three fields can only be written in GROUP BY. On the contrary, SPL can write such syntax for grouping to reduce the amount of calculation.

 

The SPL script is as follows：

	A
1	=now()
2	1995-3-15
3	>mktsegment="BUILDING"
4	=file("customer.ctx").open().cursor@m(C_CUSTKEY;C_MKTSEGMENT==mktsegment).fetch().keys@im(C_CUSTKEY)
5	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2聽 && A4.find(O_CUSTKEY))
6	=file("lineitem.ctx").open().news(A5,L_ORDERKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2)
7	=A6.groups@o(L_ORDERKEY;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY)
8	=A7.top(10;[-revenue,O_ORDERDATE])
9	return interval@ms(A1,now())

When orderly merging the primary-sub table in A6, the primary table order has been filtered and the primary keys of the primary-sub table are in the same order. Therefore, when reading the sub-table by the news method, the filtered key values in the primary table can be skipped to reduce the amount of sub-table reading.            

It should be emphasized that the lineitem must be segmented and stored according to the matching methods of orders, that is, in the data preparation stage, lineitem needs to be segmented according to L_ORDERKEY to create the composite table in order to ensure that parallel segments are not misaligned. 

The @o option is used in the group function of A7. The reason why the method of ordered grouping is adopted is we know that the joined result set is ordered to L_ORDERKEY. In this way, only the last record needs to be compared with when grouping, instead of hash calculation and comparison as in the conventional grouping, and consequently, the calculation performance is much improved.

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

 

A6 also uses the technique of filtering while creating cursor,which has been described in the previous question. This technique is also applied in A5, where A4.find means to filter out the records that foreign keys do not match without the need to be converted to pointers.

 

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	635	349	312	220	222
SPL composite 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 where columnar storage can also play a role.