Search

OakieTags

more tags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Affiliations

Home

Heavy SQLの比較

Submitted by Ichiro Obata on Fri, 2010-09-03 10:36

以前行ったHeavy SQL Top5の実行時間（秒数）にIn-Memory PQも追加してみた：
A=non parallel(all physical reads)
B=in-memory nonParallel
C=parallel(DOP=6)
D=In-Memory Parallel

SQL# A B C D
1 7.95 3.63 3.24 1.22
2 7.67 2.60 2.71 1.33
3 5.24 1.01 1.51 0.35
4 8.61 3.95 3.82 1.66
5 2.81 1.02 2.82 0.66

繰り返しになるが、In-Memory Parallel Queryでは明示的にパラレル度を設定することはできない。
しかし、前回のテストで示したようにパラレル度(DOP)は自動的に6になっている。
そして上記CのParallel(DOP=6)と比較すると2倍から5倍の性能が出ている。

1 select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in
( select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 313)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey
, o_orderkey
, o_orderdate
, o_totalprice
order by o_totalprice desc, o_orderdate;
2 select nation, o_year, sum(amount) as sum_profit
from ( select n_name as nation, extract(year from o_orderdate) as o_year
, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from part, supplier, lineitem, partsupp, orders, nation
where s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%navy%') profit
group by nation, o_year
order by nation, o_year desc;
3 select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count
, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count
from orders, lineitem
where o_orderkey = l_orderkey
and l_shipmode in ('SHIP', 'FOB')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1997-01-01'
and l_receiptdate < date '1997-01-01' + interval '1' year
group by l_shipmode
order by l_shipmode;
4 select s_name, count(*) as numwait
from supplier, lineitem l1, orders, nation
where s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists
( select * from lineitem l2
where l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey)
and not exists
( select * from lineitem l3
where l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate)
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by s_name
order by numwait desc, s_name
5 select supp_nation, cust_nation, l_year, sum(volume) as revenue
from
( select n1.n_name as supp_nation, n2.n_name as cust_nation
, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
from supplier, lineitem, orders, customer, nation n1, nation n2
where s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and ( (n1.n_name = 'JAPAN' and n2.n_name = 'ETHIOPIA')
or (n1.n_name = 'ETHIOPIA' and n2.n_name = 'JAPAN'))
and l_shipdate between date '1995-01-01'
and date '1996-12-31') shipping
group by supp_nation, cust_nation, l_year
order by supp_nation, cust_nation, l_year;