I、 Query Requirement

Q1 queries a pricing summary report of lineItem within a certain period of time, calculating different goods that has been paid, shipped or other types, including the billing, delivery, discounts, taxes, average prices and other information of the business volume.

The characteristic of Q1 is single table query operation with grouping, sorting and aggregation. This query results in 95% to 97% of the data in the table being read.

II、 Oracle Execution

The query SQL written in Oracle are as follows:

select  /*+ parallel(n) */

l_returnflag,

l_linestatus,

sum(l_quantity) as sum_qty,

sum(l_extendedprice) as sum_base_price,

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

sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

avg(l_quantity) as avg_qty,

avg(l_extendedprice) as avg_price,

avg(l_discount) as avg_disc,

count(*) as count_order

from

lineitem

where

l_shipdate <= date '1995-12-01' - interval '90' day(3)

group by

l_returnflag,

l_linestatus

order by

l_returnflag,

l_linestatus;

/*+ 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	570	356	219	170	131

 

III、 SPL optimization

This is a routine grouping query, and the result set is not large. There is no special optimization technique, and using multi-cursor to make full use of parallel is enough.            

The SPL script for writing Q1 queries is as follows:

	A
1	=now()
2	1995-12-01
3	=A2-90
4	=file("lineitem.ctx").open().cursor@m(L_SHIPDATE,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,0:L_DISC_PRICE;L_SHIPDATE<=A3)
5	=A4.run(L_DISC_PRICE=L_EXTENDEDPRICE*(1-L_DISCOUNT))
6	=A5.groups(L_RETURNFLAG, L_LINESTATUS;sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price,sum(L_DISC_PRICE):sum_disc_price, sum(L_DISC_PRICE*L_TAX):sum_charge,avg(L_QUANTITY):avg_qty,avg(L_EXTENDEDPRICE):avg_price,avg(L_DISCOUNT):avg_disc,count(1):count_order).run(sum_charge+=sum_disc_price)
7	return interval@ms(A1,now())

This code is conventional. A4 defines a multi-cursor to execute in parallel, the parallel number is determined by cursorParallelNum of the configuration file raqsoftConfig.xml. parallelNum is the maximum number of parallel allowed (the same for all subsequent examples), as shown in the following picture. Because the result set is small, groups is used in A6 for small grouping.

 

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	570	356	219	170	131
SPL composite table	336	174	91	46	38

It can be seen that the parallel effect of SPL is very excellent, close to linear speed-up.            

SPL also performs better in single threading, mainly because composite tables use compressed columnar storage.

This query involves a large amount of data and needs to read data from external storage, which makes the hard disk access time a factor that can not be ignored. When the calculation does not involve all columns, the use of columnar storage can reduce the amount of reading. Moreover, the columnar storage is easier to compress, which further reduces the hard disk access time.

In fact, esProc SPL is currently developed in Java, which should be slower than Oracle that is developed in C++, if only the computing performances of CPU are compared. However, because compressed columnar storage reduces hard disk access time, slow Java can outrun fast C++.

Nevertheless, columnar storage is not always effective. If mechanical hard disk is used, columnar storage will lead to more track-seeking time. In this way, although the amount of reading is less, the time consumption caused by track-seeking is likely to be more. This test uses SSD hard disk and has no problem of track-seeking time.

It's also worth noting that we put the filter condition in A6, which is the statement of cursor creation. In this way, while reading data, SPL will immediately give up reading the involved columns once the condition is found unsatisfied, which further reduces the time of hard disk access and record generation.

 

IV、 Further optimization

This SQL is grouping and statistics on one single table, with filtering and no join. The lineitem table has a large amount of data and takes a long time to read. If the consumption of reading can be greatly reduced in the filtering process, the performance can be further improved. If business permits, the filtering field l_shipdate could be used as dimension field when designing the composite tables, the target data can thus be quickly selected and the query speed can be improved.

The SPL script for recreating the composite table (which is only used for Q1 queries) is as follows:

 

	A
1	=file(path+"lineitem.tbl").cursor(; , "|").new(_11:L_SHIPDATE, _1:L_ORDERKEY, _4:L_LINENUMBER, _2:L_PARTKEY, _3:L_SUPPKEY,  _5:L_QUANTITY, _6:L_EXTENDEDPRICE,_7:L_DISCOUNT, _8:L_TAX, _9:L_RETURNFLAG, _10:L_LINESTATUS, _12:L_COMMITDATE, _13:L_RECEIPTDATE,_14:L_SHIPINSTRUCT,  _15:L_SHIPMODE, _16:L_COMMENT).sortx(L_SHIPDATE;4000000)
2	=file(destinate+"lineitem_Q1.ctx").create( #L_SHIPDATE,L_ORDERKEY,L_LINENUMBER,L_PARTKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX,  L_RETURNFLAG, L_LINESTATUS, L_COMMITDATE,  L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT)
3	>A2.append(A1)

 

Use this composite table for testing, and the query time is compared as follows:

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	570	356	219	170	131
SPL composite table	336	174	91	46	38
Optimized SPL composite table	276	139	76	40	34

When creating a composite table, sorting by the primary key is not always required. If we know in advance what the query condition is or whether the query condition is commonly used, the query field sorting can be used to improve performance. Composite tables sorted by primary key and by query field can coexist in use; when achieving certain query task, either of them can be flexibly selected as needed.