Performance optimization skill: orderly grouping

 

I        Problem background and applicable scenarios

  Generally, the hash scheme is adopted in grouping calculation, that is, the hash value of the grouping field is calculated first, the records with the same hash value are sorted into a small set, and then the records in the small set are traversed to find the records with the same grouping field value and aggregate into a group. The complexity (number of comparisons) of the grouping depends on the repeated value rate of the hash function. When the hash space is small, the repeated value rate will be high, the number of comparisons will be more, and the performance will be greatly reduced. In order to improve performance, we need to allocate more memory to store hash table. In addition, some data types (long strings) have slow hash calculation, which also affects performance.

  If the grouping field is orderly, when grouping, each record is only compared with the previous record. If the grouping field is different, a new group will be created; and if the grouping field is the same, it will be aggregated into the current group. The complexity of such grouping operation is n (the length of the set to be grouped), and there is no problem of hash calculation and repeated value rate. It can obtain faster performance than hash grouping, and it does not need much memory to store hash table.

  SPL provides this grouping method. Let's test it with an example and compare it with Oracle using hash grouping algorithm.

 

II      Testing environment

  The testing computer has two intel2670 CPUs, main frequency 2.6G, 16 cores in total, memory 64G, SSD. Install the virtual machine on this machine to test, and set the virtual machine to 16 core and 8G memory.

 

III    Small data amount and small result set testing

  Create the data table orderdetail_1 on the virtual machine. There are three fields: OrderID (integer), detailid (integer) and amount (real). The first two fields are primary keys, generating 80 million rows of records. Import the table into the Oracle database, and use it to generate the esProc SPL group table for testing.

  The data is arranged in ascending order by OrderID and grouped by OrderID. There are 50 groups in total. Count the total amount and the number of details of each order.

1.  Oracle testing

  Write the query test SQL as follows:

   select /*+ parallel(n)*/
      orderid, sum(amount) as amount, count(detailid) as details
   from orderdetail_1
   group by orderid;

  Where /*+ parallel(n)*/ is used for parallel test and n is the parallel number.

2.  SPL testing

  Write the SPL script as follows:


A

1

=now()

2

=file("/home/ctx/orderdetail_1.ctx").create().cursor@m(orderid,detailid,amount;;1)

3

=A2.groups@o(orderid;sum(amount):amount,count(detailid):details)

4

=interval@s(A1,now())

  The option @o of the groups function applies to the situation where the grouping field is ordered, and only the values of adjacent rows are compared for orderly grouping.

3.  Testing result

  The testing result is as follows, in seconds:

Number of parallel

1

2

4

8

16

Oracle

24

19

16

13

13

SPL

11

6

3

2

1

  In the case of 80 million rows of data, the performance of SPL orderly grouping is doubled, and the parallel effect is very good, and the performance shows a linear increase. However, the effect of Oracle parallel speed-up using hash grouping is not obvious.

  The degree of performance improvement is related to the amount of data. When the amount of data is very small, the proportion of grouping time to the whole query time is very small, and the overall performance improvement is not obvious. However, with the increase of data volume, the improvement effect will become more and more significant.

  Let's take a look at the big data testing. 

 

IV   Large data amount and large result set testing

  Create the data table orderdetail_2 on the virtual machine. There are three fields: OrderID (string), detailid (integer) and amount (real). The first two fields are primary keys, generating 2.4 billion rows of records. Import the table into the Oracle database, and use it to generate the esProc SPL group table for testing.

  The data is arranged in ascending order by OrderID and grouped by OrderID, with a total of 800 million groups. Count the total amount and the number of details of each order. Because it takes a long time for the large result set to be output in Oracle, the grouping result will be filtered again, and only the orders whose total order amount is less than 35 yuan will be output. There are only 12 results, so the output will hardly take up the time.

1.  Oracle testing

  Write the query test SQL as follows:

   select * from (
      select /*+ parallel(n)*/
         orderid, sum(amount) sum_amount, count(detailid) as details
      from orderdetail_2
      group by orderid
      )
   where sum_amount<35;

  Where /*+ parallel(n)*/ is used for parallel test and n is the parallel number.

2.  SPL testing

  Write the SPL script as follows:


A

1

=now()

2

=file("/home/ctx/orderdetail_2.ctx").create().cursor@m(orderid,detailid,amount;;1)

3

=A2.group(orderid;sum(amount):amount,count(detailid):details).select(amount<35).fetch()

4

=interval@s(A1,now())

  Because the result set of grouping is too large to be loaded into memory, the group function is used to group in order, return the cursors corresponding to the result set, and then filter the cursors to get the required query result.

3.  Testing result

  The testing result is as follows, in seconds:

Number of parallel

1

2

4

8

16

Oracle

2647

1345

1092

806

737

SPL

451

235

119

65

48

  In the case of none parallel, the performance of SPL orderly grouping is nearly six times higher than that of Oracle. Because SPL orderly grouping is very suitable for parallel, with the increase of the number of parallel, the effect of performance improvement is better.