再议SAP IQ 对”分页”查询的支持

    从SAP IQ 15.4开始，通过在SELECT语句中增加LIMIT子句可以实现”分页”查询。在本人的blog： http://blog.chinaunix.net/uid-16765068-id-3785581.html 中讲述了这个特性。不过这个特性用于记录数较多的表时，存在性能问题，对于IQ 15.4 ESD#1、ESD#2、ESD#3以及 IQ 16.0 SP02之前的版本都是如此。从SAP IQ 16.0  SP02开始修复了这个bug。

     本文将结合一个例子进行说明，并对SAP IQ 16.0 SP02进行了验证。

   1. 创建测试表  

    使用TPC-H模型中的lineitm表进行测试。见表语句如下：

create table lineitem

(

l_orderkey int not null,

l_partkey int,

l_suppkey int,

l_linenumber int not null,

l_quantity numeric(10,2),

l_extendedprice numeric(10,2),

l_discount numeric(10,2),

l_tax numeric(10,2),

l_returnflag char(1),

l_linestatus char(1),

l_shipdate date,

l_commitdate date,

l_receiptdate date,

l_shipinstruct char(25),

l_shipmode char(10),

l_comment varchar(44),

primary key(l_orderkey,l_linenumber)

)

     分别在IQ 15.4 ESD#3和IQ 16.0 SP02的两个IQ库中创建表，以便进行查询对比。

   2. 生成测试数据  

    生成TPC-H 1GB 测试数据(lineitem表6百万条记录)，具体方法参见： http://blog.chinaunix.net/uid-16765068-id-126261.html

   3. 使用load table装载lineitem表  

    load table语句如下：

BEGIN

declare startDate datetime;

declare endDate datetime;

set startDate=getDate();

LOAD TABLE lineitem
(
  l_orderkey    ‘|’     ,
  l_partkey     ‘|’     ,
  l_suppkey     ‘|’     ,
  l_linenumber  ‘|’     ,
  l_quantity    ‘|’     ,
  l_extendedprice       ‘|’     ,
  l_discount    ‘|’     ,
  l_tax ‘|’     ,
  l_returnflag  ‘|’     ,
  l_linestatus  ‘|’     ,
  l_shipdate    ‘|’     ,
  l_commitdate  ‘|’     ,
  l_receiptdate ‘|’     ,
  l_shipinstruct        ‘|’     ,
  l_shipmode    ‘|’     ,
  l_comment     ‘|’
)
FROM ‘/home/sybiq/tpch/generate/gen_data/lineitem.tbl.1′,’/home/sybiq/tpch/generate/gen_data/lineitem.tbl.2’,
   ‘/home/sybiq/tpch/generate/gen_data/lineitem.tbl.3′,’/home/sybiq/tpch/generate/gen_data/lineitem.tbl.4’
FORMAT ASCII
ESCAPES OFF
QUOTES OFF
NOTIFY 500000
ROW DELIMITED BY ‘\x0a’
WITH CHECKPOINT ON;
COMMIT;

set endDate=getDate();
message ‘[load lineitem execute time is : ‘,datediff(ms,startDate,endDate),’ms]’ type info to client;
END;

4. 执行”分页查询”  
    使用dbisql工具分别针对IQ 15.4 ESD#3 和 IQ 1.0 SP02执行如下查询：
select * from lineitem where l_shipmode = ‘AIR’ and l_quantity >= 1.00 order by l_quantity desc limit 10 offset 100000
    查询执行时间如下：
         * IQ 15.4 ESD#3 ：   41.9秒
         * IQ 16.0 SP02：      0.83秒
    提升了将近50倍！！

    注意：数据库选项 force_no_scroll_cursors 要设置为‘off’ ，否则查询性能还是比较差的。