Performance optimization skill: Attached Table

 

I. Problem introduction & Solution

  Though we already have order-based MERGE scheme to boost the performance of join between a main table and its subtable (See Performance Optimization Tricks: Order-based MERGE), we never intend to stop our work in finding a faster one. Our latest result is using esProc attached table to speed up the main-sub tables join. In esProc, a composite table consists of multiple tables, such as a main table and its subtable, in one file. We create a composite table file from the main table and then attach the subtable to the main table. The subtable is thus an attached table that associates with the main table through a field pointing to the main table’s dimension field.

  A composite table stores only the associated field for an attached table. The storage structure can reduce the disk read actions when retrieving the attached table. For the main table and subtable, it stores it in the main table and doesn’t store the subtable physically. The subtable is attached to the main table through the associated field, which is equivalent to a pre-join. This helps reduce the comparisons for a join and make the operation faster.

  In the following part we’ll look at how faster a join using the attached table is than that using the order-based MERGE scheme.

 

II. Test environment

  There are two test computers. Each has an Intel2670 16-core, 2.6 GHz processor, a 64GB RAM and an SSD.

  A total of 200G data has been generated according to TPCH benchmark. The main table is called orders and the subtable is lineitem). The two tables are ordered respectively by O_ORDERKEY and L_ORDERKEY in ascending order.

 

III. Generating an attached table

  First we generate an attached table file from the composite table files orders.ctx and lineitem.ctx. They were repeatedly used in our performance optimization tricks series articles. Below is the SPL script for doing it:


A

1

=file(path+"orders.ctx").create().cursor()

2

=file(path+"lineitem.ctx").create().cursor(L_ORDERKEY:O_ORDERKEY,    L_LINENUMBER, L_PARTKEY,L_SUPPKEY,  L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,   L_TAX,  L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,   L_COMMITDATE,  L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE)

3

=file(path+"orders_lineitem.ctx").create(  #O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,   O_ORDERDATE,O_ORDERPRIORITY,O_SHIPPRIORITY)

4

=A3.attach(lineitem,  #L_LINENUMBER, L_PARTKEY,L_SUPPKEY,  L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,   L_TAX,  L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,   L_COMMITDATE,  L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE)

5

=A3.append@i(A1)

6

=A4.append@i(A2)

  The expression “main table.attach(subtable,…) attaches the subtable to the main table.

 

IV. Tests

  The SPL test script using the order-based MERGE algorithm, in which A1 defines the number of parallel tasks:


A

1

1

2

=now()

3

=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERDATE;;A1)

4

=file(path+"lineitem.ctx").create().cursor(L_ORDERKEY,L_EXTENDEDPRICE,L_DISCOUNT;;A3)

5

=joinx(A4:detail,L_ORDERKEY;A3:orders,O_ORDERKEY)

6

=A5.groups(year(orders.O_ORDERDATE):l_year;    sum(detail.L_EXTENDEDPRICE * (1 -   detail.L_DISCOUNT)):revenue)

7

=interval@s(A2,now())

 

  The SPL test script using the attached table:


A

1

1

2

=now()

3

=file(path+"orders_lineitem.ctx").create()

4

=A3.attach(lineitem)

5

=A4.cursor@m(L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE;;A1)

6

=A5.groups(year(O_ORDERDATE):l_year;     sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)):revenue)

7

=interval@s(A2,now())

 

  Test results (Unit: sec):

Parallel task count

1

2

4

8

16

Attached table

427

218

116

62

46

Order-based MERGE

675

361

171

92

64

 

V. Summary

  According to the test results, the attached table is 40% faster than the order-based MERGE in performing a main-sub table join. 

  We also made more tests and found that, since the ratio of the record count in a main table to that in its subtable is 1:N, the bigger the value of N or when the main table’s primary key consists of more than one field, the less it takes in disk reading and key comparisons and thus the more the performance is boosted.