IQ性能调优示例——执行TPCH定义的查询

    在装载完TPCH 1GB数据之后启动IQ Server，执行TPCH定义的一些查询，看看IQ执行查询的速度如何。下面是本人机器上的IQ设置。

iq server 启动选项

-n  tpch_srv
-x  tcpip{port=3638}
-cl 64m
-ch 128m
-gc 5000
-gr 5000
-gd dba
-gk dba
-gl all
-gm 10
-gp 4096
-ti 4400
-tl 300
-iqmc 150
-iqtc 150 
  
iq数据库选项

set option public.Force_No_Scroll_Cursors='On';
set option public.Minimize_Storage='On';
set option public.Notify_Modulus=200000;
set option public.Subquery_Flattening_Preference=3;
set option public.allow_read_client_file='On';
set option tpch_user.default_dbspace='TPCH_USER_MAIN';

   本人挑选了TPCH定义的查询1、3、9、18、21等5个查询，这些查询是很多DBMS厂家执行时间较长的。我们就看看IQ执行的效果吧，有兴趣读者的可以在同一环境下对比一下其他DBMS的执行情况。

 

1. Query 1

 

   执行时间(取三次试行的平均时间)：5.962秒

   查询语句：

 

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 <= dateadd(day,-90,'1998-12-01')
group by l_returnflag,l_linestatus
order by l_returnflag,l_linestatus;

 

2. Query 3

 

   执行时间(取三次试行的平均时间)：0.761秒

   查询语句：

 

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 < '1995-03-12'
  and l_shipdate > '1995-03-15'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate;

 

3. Query 9

 

   执行时间(取三次试行的平均时间)：3.039秒

   查询语句：

 

select
        nation,
        o_year,
        sum(amount) as sum_profit
from
        (
                select
                        n_name as nation,
                        year(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;

 

4. Query 18

 

   执行时间(取三次试行的平均时间)：1.441秒

   查询语句：

 

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;

 

5. Query 21

 

   执行时间(取三次试行的平均时间)：2.327秒

   查询语句：

 

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;