一、???? 查詢要求
Q1語句查詢lineItem的一個定價總結報告。在單個表lineitem上查詢某個時間段內(nèi)，對已經(jīng)付款的、已經(jīng)運送的等各類商品進行統(tǒng)計，包括業(yè)務量的計費、發(fā)貨、折扣、稅、平均價格等信息。
Q1語句的特點是：帶有分組、排序、聚集操作并存的單表查詢操作。這個查詢會導致表上的數(shù)據(jù)有95%到97%行被讀取到。
二、???? Oracle執(zhí)行
Oracle編寫的查詢SQL語句如下：
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) */ 是Oracle的并行查詢語法，n就是并行數(shù)。
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle570356219170131
三、???? SPL優(yōu)化
這是一個常規(guī)的分組查詢，結果集也不大，沒有特殊的優(yōu)化技術，使用多路游標充分利用并行即可。
編寫Q1查詢的SPL腳本如下：
A
1=1
21995-12-01
3=A2-90
4=now()
5=file(path+"lineitem.ctx").create()
6=A5.cursor@m(L_SHIPDATE,L_QUANTITY, ? L_EXTENDEDPRICE, L_DISCOUNT,? L_TAX, ? L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A3;A1)
7=A6.groups(L_RETURNFLAG, ? L_LINESTATUS; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, ? sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)):sum_disc_price, sum(L_EXTENDEDPRICE * ? (1 - L_DISCOUNT) * (1 + L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, ? avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, ? count(1):count_order)
8=interval@s(A4,now())
其中A1格為設置的并行數(shù)量，后續(xù)其它例子均這樣約定。
這段代碼較為常規(guī)，A6定義多路游標利用并行，因結果集不大，在A7使用groups做小分組。
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle570356219170131
SPL組表336174914638
可以看出，SPL的并行效果很好，接近線性提速。
單線程時SPL的性能也更好，這主要是因為組表采用了壓縮列式存儲。
本查詢涉及數(shù)據(jù)量較大，需要從外存讀入數(shù)據(jù)，硬盤訪問時間是不可忽略的因素。當計算未涉及全部數(shù)據(jù)列時，使用列式存儲能減少讀取量。而且，列存方式更容易壓縮，從而進一步減少硬盤訪問時間。
事實上，集算器SPL目前采用Java開發(fā)，如果單純對比CPU的計算性能，應當會弱于C++開發(fā)的Oracle。但是，因為壓縮列式存儲減少了硬盤訪問時間，這導致了慢速的Java也能跑過快速的C++。
不過，列存并非總是有效，如果采用機械硬盤，列式存儲會導致更多的尋道時間，雖然讀取量變少，但由于尋道導致的時間消耗很可能更多。而這次測試采用了SSD硬盤，沒有尋道時間的問題。
還需要值得注意的是，我們把過濾條件寫進了A6，也就是游標建立的語句中。這樣，SPL在讀取數(shù)據(jù)時，如果發(fā)現(xiàn)條件不成立，將直接放棄讀取相關列，進一步減少硬盤訪問和記錄生成的時間。
四、???? 進一步優(yōu)化
這個SQL是單表分組統(tǒng)計，沒有關聯(lián)，有過濾。lineitem這張表數(shù)據(jù)量很大，讀數(shù)耗時很長，如果能在過濾環(huán)節(jié)大幅度減少讀數(shù)的消耗，可以進一步提升性能。在業(yè)務許可時，如果設計組表時改用過濾字段l_shipdate作為維字段，則可以快速選出目標數(shù)據(jù)，提升查詢速度。
重新生成組表(此組表僅用于Q1查詢)的SPL腳本如下：
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)
用此組表測試，查詢時間對比如下：
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle570356219170131
SPL組表336174914638
優(yōu)化的SPL組表276139764034
創(chuàng)建組表時不一定總按主鍵排序，如果事先知道查詢條件或者這個查詢條件很常用，則可以用查詢字段排序來提高性能。在使用中可以采取主鍵排序的組表和查詢字段排序的組表共存的策略，編寫某個查詢?nèi)蝿諘r可根據(jù)需要靈活選用某個組表。
