性能优化技巧：附表
乾学院
Posted on
| A | |
| 1 | =file(path+"orders.ctx").open().cursor() |
| 2 | =file(path+"lineitem.ctx").open().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) |
用主表.attach(子表,…)来建立主子表间的依附关系。
| A | |
| 1 | 1 |
| 2 | =now() |
| 3 | =file(path+"orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE;;A1) |
| 4 | =file(path+"lineitem.ctx").open().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()) |
A1单元格是测试并行数。
| A | |
| 1 | 1 |
| 2 | =now() |
| 3 | =file(path+"orders_lineitem.ctx").open() |
| 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()) |
测试结果列表如下(单位：秒)：
| 并行数 | 1 | 2 | 4 | 8 | 16 |
| 附表 | 427 | 218 | 116 | 62 | 46 |
| 有序归并 | 675 | 361 | 171 | 92 | 64 |