此篇文档只介绍了如何在DM8上使用测试工具进行tpch测试的步骤，关于参数性能优化方面的内容不做介绍。
更多达梦数据库相关问题，请浏览云适配技术社区。
达梦云适配技术社区
https://eco.dameng.com/1.准备tpch测试工具benchmark
2.生成.tbl数据文件
利用degen工具模拟生成1G的数据
cd /ceshi/tpch/linux/tpch_dbgen
./dbgen -s 1会在当前目录下生成数据源文件，用来之后dmfldr导入的元数据。
3.修改每个表的dmfldr控制文件
修改下面8个表对应的导入控制文件
vi customer.ctrl
vi lineitem.ctrl
vi nation.ctrl
vi orders.ctrl
vi part.ctrl
vi partsupp.ctrl
vi region.ctrl
vi supplier.ctrl
以下是customer表的示例修改
4创建表的结构
执行下面的sql语句用来创建表的结构
drop table customer;
drop table lineitem;
drop table nation;
drop table orders;
drop table part;
drop table partsupp;
drop table region;
drop table supplier;
create huge table CUSTOMER
(
C_CUSTKEY int not null,
C_NAME varchar(25) not null,
C_ADDRESS varchar(40) not null,
C_NATIONKEY int not null,
C_PHONE char(15) not null,
C_ACCTBAL float not null,
C_MKTSEGMENT char(10) not null,
C_COMMENT varchar(117) not null,
primary key (C_CUSTKEY)
);
create huge table LINEITEM
(
L_ORDERKEY int not null,
L_PARTKEY int not null,
L_SUPPKEY int not null,
L_LINENUMBER int not null,
L_QUANTITY float not null,
L_EXTENDEDPRICE float not null,
L_DISCOUNT float not null,
L_TAX float not null,
L_RETURNFLAG char(1) not null,
L_LINESTATUS char(1) not null,
L_SHIPDATE date not null,
L_COMMITDATE date not null,
L_RECEIPTDATE date not null,
L_SHIPINSTRUCT char(25) not null,
L_SHIPMODE char(10) not null,
L_COMMENT varchar(44) not null,
primary key(L_ORDERKEY , L_LINENUMBER)
);
create huge table NATION
(
N_NATIONKEY int not null,
N_NAME char(25) not null,
N_REGIONKEY int not null,
N_COMMENT varchar(152) not null,
primary key (N_NATIONKEY)
);
create huge table ORDERS
(
O_ORDERKEY int not null,
O_CUSTKEY int not null,
O_ORDERSTATUS char(1) not null,
O_TOTALPRICE float not null,
O_ORDERDATE date not null,
O_ORDERPRIORITY char(15) not null,
O_CLERK char(15) not null,
O_SHIPPRIORITY integer not null,
O_COMMENT varchar(79) not null,
primary key(O_ORDERKEY)
);
create huge table part
(
P_PARTKEY int not null,
P_NAME varchar(55) not null,
P_MFGR char(25) not null,
P_BRAND char(10) not null,
P_TYPE varchar(25) not null,
P_SIZE int not null,
P_CONTAINER char(10) not null,
P_RETAILPRICE float not null,
P_COMMENT varchar(23) not null,
primary key (P_PARTKEY)
);
create huge table PARTSUPP
(
PS_PARTKEY int not null,
PS_SUPPKEY int not null,
PS_AVAILQTY int not null,
PS_SUPPLYCOST float not null,
PS_COMMENT varchar(199) not null,
primary key (PS_PARTKEY , PS_SUPPKEY)
);
create huge table REGION
(
R_REGIONKEY int not null,
R_NAME char(25) not null,
R_COMMENT varchar(152) not null,
primary key (R_REGIONKEY)
);
create huge table SUPPLIER
(
S_SUPPKEY int not null,
S_NAME char(25) not null,
S_ADDRESS varchar(40) not null,
S_NATIONKEY int not null,
S_PHONE char(15) not null,
S_ACCTBAL float not null,
S_COMMENT varchar(101) not null,
primary key (S_SUPPKEY)
);
5.加载数据
使用dmfldr工具导入数据
cd /home/dmdba/dmdbms/bin/
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/customer.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/lineitem.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/nation.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/orders.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/partsupp.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/part.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/region.ctrl\'
./dmfldr userid=SYSDBA/SYSDBA:5236 control=\'/ceshi/tpch/linux/supplier.ctrl\'
6.更新统计信息
执行下面的sql信息更新统计信息
--删除表上所有列的统计信息
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'CUSTOMER');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'LINEITEM');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'NATION');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'ORDERS');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'PART');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'PARTSUPP');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'REGION');
CALL SP_TAB_COL_STAT_DEINIT ('SYSDBA', 'SUPPLIER');
--删除表的统计信息
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'CUSTOMER');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'LINEITEM');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'NATION');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'ORDERS');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'PART');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'PARTSUPP');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'REGION');
CALL SP_TAB_STAT_DEINIT ('SYSDBA', 'SUPPLIER');
--更新统计信息
SP_TAB_STAT_INIT('SYSDBA','REGION');
SP_TAB_STAT_INIT('SYSDBA','NATION');
SP_TAB_STAT_INIT('SYSDBA','PART');
SP_TAB_STAT_INIT('SYSDBA','PARTSUPP');
SP_TAB_STAT_INIT('SYSDBA','SUPPLIER');
SP_TAB_STAT_INIT('SYSDBA','CUSTOMER');
SP_TAB_STAT_INIT('SYSDBA','ORDERS');
SP_TAB_STAT_INIT('SYSDBA','LINEITEM');
STAT 100 ON REGION(R_NAME) ;
STAT 100 ON REGION(R_REGIONKEY) ;
STAT 100 ON NATION(N_NAME) ;
STAT 100 ON NATION(N_NATIONKEY) ;
STAT 100 ON NATION(N_REGIONKEY) ;
STAT 100 ON SUPPLIER(S_SUPPKEY) ;
STAT 100 ON SUPPLIER(S_NATIONKEY) ;
STAT 100 ON SUPPLIER(S_COMMENT) ;
STAT 100 ON PART(P_SIZE);
STAT 100 ON PART(P_BRAND);
STAT 100 ON PART(P_TYPE);
STAT 100 ON PART(P_NAME);
STAT 100 ON PART(P_PARTKEY);
STAT 100 ON PART(P_CONTAINER);
STAT 100 ON PARTSUPP(PS_SUPPKEY);
STAT 100 ON PARTSUPP(PS_PARTKEY);
STAT 100 ON ORDERS(O_ORDERKEY);
STAT 100 ON ORDERS(O_ORDERDATE);
STAT 100 ON ORDERS(O_ORDERSTATUS);
STAT 100 ON ORDERS(O_ORDERPRIORITY);
STAT 100 ON ORDERS(O_CUSTKEY);
STAT 100 ON ORDERS(O_COMMENT);
STAT 100 ON LINEITEM(L_SUPPKEY);
STAT 100 ON LINEITEM(L_PARTKEY);
STAT 100 ON LINEITEM(L_ORDERKEY);
STAT 100 ON LINEITEM(L_SHIPDATE);
STAT 100 ON LINEITEM(L_SHIPMODE);
STAT 100 ON LINEITEM(L_COMMITDATE);
STAT 100 ON LINEITEM(L_RECEIPTDATE);
STAT 100 ON LINEITEM(L_RETURNFLAG);
STAT 100 ON LINEITEM(L_LINESTATUS);
STAT 100 ON LINEITEM(L_QUANTITY);
STAT 100 ON LINEITEM(L_SHIPINSTRUCT);
STAT 100 ON CUSTOMER(C_CUSTKEY);
STAT 100 ON CUSTOMER(C_MKTSEGMENT);
STAT 100 ON CUSTOMER(C_NATIONKEY);
STAT 100 ON CUSTOMER(C_ACCTBAL);7.测试语句执行时间
下方sql为测试的sql语句
--Q1(LINEITEM)
select
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'1998-12-01' - interval '90' day
group by l_returnflag, l_linestatus
order by l_returnflag, l_linestatus;
--Q2
select TOP 100
s_acctbal, s_name,
n_name, p_partkey, p_mfgr,
s_address, s_phone, s_comment
from part, supplier, partsupp, nation, region
where p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost =
( select min(ps_supplycost)
from partsupp, supplier, nation, region
where p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE' )
order by s_acctbal desc, n_name, s_name, p_partkey;
--Q3
select TOP 10
l_orderkey,
sum(l_extendedprice*(1-l_discount)) as revenue,
o_orderdate,
o_shippriority
from customer, orders, lineitem
where c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date'1995-03-15'
and l_shipdate > date'1995-03-15'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate;
--Q4
select
o_orderpriority,
count(*) as order_count
from orders
where o_orderdate >= date'1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists
(select *
from lineitem
where l_orderkey = o_orderkey
and l_commitdate < l_receiptdate )
group by o_orderpriority
order by o_orderpriority;
--Q5
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from customer, orders, lineitem, supplier, nation, region
where c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= date'1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by n_name
order by revenue desc;
--Q6
select
sum(l_extendedprice*l_discount) as revenue
from lineitem
where l_shipdate >= '1994-01-01'
and l_shipdate < dateadd(year,1, '1994-01-01')
and l_discount between 0.06 - 0.01
and 0.06 + 0.01
and l_quantity < 24;
--Q7
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 = 'FRANCE' and n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE') )
and l_shipdate between date'1995-01-01' and date'1996-12-31' ) as shipping
group by supp_nation, cust_nation, l_year
order by supp_nation, cust_nation, l_year;
--Q8
select
o_year,
sum(case when nation = 'BRAZIL' then volume else 0 end) / sum(volume) as mkt_share
from ( select extract(year from o_orderdate) as o_year,
l_extendedprice * (1-l_discount) as volume,
n2.n_name as nation
from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
where p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01'
and date '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL' ) as all_nations
group by o_year
order by o_year;
--Q9
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 '%green%' ) as profit
group by nation, o_year
order by nation, o_year desc;
--Q10
select top 20
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address, c_phone,
c_comment
from customer, orders, lineitem, nation
where c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date'1993-10-01'
and o_orderdate < date'1993-10-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
order by revenue desc;
--Q11
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as valuess
from partsupp, supplier, nation
where ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by ps_partkey
having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001
from partsupp, supplier, nation
where ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY' )
order by valuess desc;
--Q12
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 ('MAIL', 'SHIP')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1994-01-01'
and l_receiptdate < date '1994-01-01' + interval '1' year
group by l_shipmode
order by l_shipmode;
--Q13
select
c_count,
count(*) as custdist
from ( select c_custkey,
count(o_orderkey)
from customer left outer join orders on c_custkey = o_custkey
and o_comment not like '%special%requests%'
group by c_custkey )as c_orders (c_custkey, c_count)
group by c_count
order by custdist desc, c_count desc;
--Q14
select
100.00 * sum(case when p_type like 'PROMO%'
then l_extendedprice*(1-l_discount)
else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from lineitem, part
where l_partkey = p_partkey
and l_shipdate >= date '1995-09-01'
and l_shipdate < date'1995-09-01' + interval '1' month;
--Q15
create view revenue (supplier_no, total_revenue) as
select l_suppkey, sum(l_extendedprice * (1 - l_discount)) from lineitem
where l_shipdate >= date '1996-01-01' and l_shipdate < date '1996-01-01' + interval '3' month group by l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from supplier, revenue
where s_suppkey = supplier_no
and total_revenue =
( select max(total_revenue) from revenue ) order by s_suppkey;
drop view revenue;
--Q16
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from partsupp, part
where p_partkey = ps_partkey
and p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps_suppkey not in ( select s_suppkey
from supplier
where s_comment like '%Customer%Complaints%' )
group by p_brand, p_type, p_size
order by supplier_cnt desc, p_brand, p_type, p_size;
--Q17
select
sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part
where p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey );
--Q18
select top 100
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) > 300 )
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;
--Q19
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from lineitem, part
where ( p_partkey = l_partkey
and p_brand = 'Brand#12'
and p_container in ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1
and l_quantity <= 1 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON' )
or ( p_partkey = l_partkey and p_brand = 'Brand#23'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10
and l_quantity <= 10 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON' )
or ( p_partkey = l_partkey
and p_brand = 'Brand#34'
and p_container in ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 20
and l_quantity <= 20 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON' );
--Q20
select
s_name,
s_address
from supplier, nation
where s_suppkey in ( select ps_suppkey from partsupp
where ps_partkey in ( select p_partkey from part where p_name like 'forest%' )
and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year )
)
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by s_name;
--Q21
select top 100
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 = 'SAUDI ARABIA'
group by s_name
order by numwait desc, s_name;
--Q22
select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal
from customer
where substring(c_phone from 1 for 2) in ('13','31','23','29','30','18','17')
and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00
and substring (c_phone from 1 for 2) in ('13','31','23','29','30','18','17')
)
and not exists ( select * from orders where o_custkey = c_custkey )
) as custsale
group by cntrycode
order by cntrycode;下方为Q22的测试运行时间
1040
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
