selectl_year,sum(volume) as revenuefrom(selectextract(year from l_shipdate) as l_year,(l_extendedprice * (1 - l_discount) ) as volumefromorderdetail,partwherep_partkey = l_partkeyand length(p_type)>2) shippinggroup byl_yearorder byl_year;
selectl_year,sum(volume) as revenuefrom(selectextract(year from l_shipdate) as l_year,(l_extendedprice * (1 - l_discount) ) as volumefromsupplier,orderdetail,orders,customer,part,nation n1,nation n2wheres_suppkey = l_suppkeyand p_partkey = l_partkeyand o_orderkey = l_orderkeyand c_custkey = o_custkeyand s_nationkey = n1.n_nationkeyand c_nationkey = n2.n_nationkeyand length(p_type) > 2and n1.n_name is not nulland n2.n_name is not nulland s_suppkey > 0) shippinggroup byl_yearorder byl_year;
| 两表关联 | 六表关联 | |
| 运行时间(秒) | 26 | 167 |
两个查询语句都用了嵌套写法,Oracle自动优化后的计算性能比无嵌套时还要好一些(无嵌套时group by和select有可能会有重复计算)。
| A | |
| 1 | >env(region,file(path+"region.ctx").open().memory().keys@i(R_REGIONKEY)) |
| 2 | >env(nation,file(path+"nation.ctx").open().memory().keys@i(N_NATIONKEY)) |
| 3 | >env(supplier,file(path+"supplier.ctx").open().memory().keys@i(S_SUPPKEY)) |
| 4 | >env(customer,file(path+"customer.ctx").open().memory().keys@i(C_CUSTKEY)) |
| 5 | >env(part,file(path+"part.ctx").open().memory().keys@i(P_PARTKEY)) |
| 6 | >env(orders,file(path+"orders.ctx").open().memory().keys@i(O_ORDERKEY)) |
| 7 | >env(orderdetail,file(path+"orderdetail.ctx").open().memory()) |
| 8 | >nation.switch(N_REGIONKEY,region) |
| 9 | >customer.switch(C_NATIONKEY,nation) |
| 10 | >supplier.switch(S_NATIONKEY,nation) |
| 11 | >orders.switch(O_CUSTKEY,customer) |
| 12 | >orderdetail.switch(L_ORDERKEY,orders;L_PARTKEY,part;L_SUPPKEY,supplier) |
脚本中前7行分别将7个组表读入内存,生成内表,并设成全局变量。后5行完成表间连接。在SPL服务器启动时,就先运行此脚本,完成环境准备。
| A | |
| 1 | =orderdetail.select(len(L_PARTKEY.P_TYPE)>2) |
| 2 | =A1.groups(year(L_SHIPDATE):l_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
编写SPL脚本如下:
| A | |
| 1 | =orderdetail.select(len(L_PARTKEY.P_TYPE)>2 && L_ORDERKEY.O_CUSTKEY.C_NATIONKEY.N_NAME!=null && L_SUPPKEY.S_NATIONKEY.N_NAME!=null && L_SUPPKEY.S_SUPPKEY>0 ) |
| 2 | =A1.groups(year(L_SHIPDATE):l_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
预关联后,SPL代码也非常简单,关联表的字段直接可以作为本表字段的子属性访问,很易于理解。
| 两表关联 | 六表关联 | |
| 运行时间(秒) | 28 | 56 |
六表关联仅仅比两表关联慢2倍,基本上就是增加的计算量(引用这些关联表字段)的时间,而因为有了预关联,关联运算本身不再消耗时间。
| 运行时间(秒) | 两表关联 | 六表关联 | 性能降低倍数 |
| SQL | 26 | 167 | 6.4 |
| SPL预关联 | 28 | 56 | 2 |
六表关联比两表关联,SQL慢了6.4倍,说明SQL处理JOIN消耗CPU很大,性能降低明显。而采用预关联机制后的SPL只慢2倍,多JOIN几个表不再出现明显的性能下降。
更多性能优化技巧,可在底部“阅读原文”中查看