DM8达梦数据库tpch测试步骤_tpch怎么执行

作者：我家自动化 | 2024-08-06 03:06:56

踩

tpch怎么执行

此篇文档只介绍了如何在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的测试运行时间

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/935543?site