informix 分页

最新推荐文章于 2022-07-29 15:55:06 发布

yutonging2016

最新推荐文章于 2022-07-29 15:55:06 发布

阅读量1.8k

点赞数

分类专栏： informix-Administrator informix-sql-plsql 文章标签： procedure function e informix

本文链接：https://blog.csdn.net/yutonging2016/article/details/70755692

版权

本文探讨了在 Informix 数据库中进行分页查询的两种方法：使用 ROWNUM 和使用 SKIP 语句，并通过实际测试比较了它们的性能。结果显示，使用 SKIP 语句在大数据量查询时具有更好的性能。此外，还介绍了如何通过存储过程和临时表进一步优化分页查询。

摘要由CSDN通过智能技术生成

/***********************************************************************/
informix 分页处理
/***********************************************************************/
一设置执行时间显示

export DBACCESS_SHOW_TIME=1

二测试SQL
> set explain on;
执行计划见: /home/informix/sqexplain.out

1 使用ROWNUM 无效率
> select * from ( select tmp_page.*, rownum row_id from ( select t1.id
, t1.user_id ,
t1.user_name , t1.address ,
t1.matter , t1.lendmoney ,
t1.traffic , t1.leavedate
, t1.backdate , t1.summary
, t1.allowance , t1.last_update_date
, t1.last_updated_by , t1.creation_date
, t1.created_by , t1.last_update_ip
, t1.version , t1.attribute_01
, t1.attribute_02 , t1.attribute_03
, t1.attribute_04 , t1.attribute_05
, t1.attribute_06 , t1.attribute_07
, t1.attribute_08 , t1.attribute_09
, t1.attribute_10 from demo_business_trip
t1 order by t1.creation_date desc ) tmp_page where rownum <= 45 )
where row_id > 30;

15 row(s) retrieved.

Elapsed time: 30.445 sec

> select * from ( select tmp_page.*, rownum row_id from ( select t1.id
, t1.user_id ,
t1.user_name , t1.address ,
t1.matter , t1.lendmoney ,
t1.traffic , t1.leavedate
, t1.backdate , t1.summary
, t1.allowance , t1.last_update_date
, t1.last_updated_by , t1.creation_date
, t1.created_by , t1.last_update_ip
, t1.version , t1.attribute_01
, t1.attribute_02 , t1.attribute_03
, t1.attribute_04 , t1.attribute_05
, t1.attribute_06 , t1.attribute_07
, t1.attribute_08 , t1.attribute_09
, t1.attribute_10 from demo_business_trip
t1 order by t1.creation_date desc ) tmp_page where rownum <= 6000045 )
where row_id > 6000030;

15 row(s) retrieved.

Elapsed time: 57.142 sec

2 使用 skip 语句提高性能比较

--返回 rowid和所有字段
> select skip 0 first 15 rowid, * from demo_business_trip t1 order by t1.creation_date desc;

Elapsed time: 0.004 sec

The final cost of the plan is reduced because of the FIRST n specification in
the query.

Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 15 6311654 15 00:00.00 3

> select skip 6200036 first 15 rowid, * from demo_business_trip t1 order by t1.creation_date desc;

Elapsed time: 7.648 sec

The final cost of the plan is reduced because of the FIRST n specification in
the query.

Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6200051 6311654 6200051 00:07.64 1289790

--只返回 rowid

> select skip 6200036 first 15 rowid from demo_business_trip t1 order by t1.creation_date desc;

Elapsed time: 1.686 sec

The final cost of the plan is reduced because of the FIRST n specification in
the query.

Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6200051 6311654 6200051 00:01.68 190197

/*****************************************************************************************************/
下面2例使用分别使用了存储过程与临时表和函数与LIST 的组合
比较结果:存储过程与临时表比函数与LIST 的组合稍微快些，但是函数与LIST 的组合直接返回结果
/******************************************************************************************************/

3 使用存储过程，临时表
测试表结构: DEMO_BUSINESS_TRIP

CREATE TABLE DEMO_BUSINESS_TRIP(
ID VARCHAR(50) NOT NULL,
USER_ID VARCHAR(50) NOT NULL,
USER_NAME VARCHAR(50),
ADDRESS VARCHAR(100),
MATTER LVARCHAR(500),
LENDMONEY INT8,
TRAFFIC VARCHAR(50),
LEAVEDATE DATETIME YEAR TO FRACTION (5),
BACKDATE DATETIME YEAR TO FRACTION (5),
SUMMARY LVARCHAR(4000),
ALLOWANCE DECIMAL(16,2),
LAST_UPDATE_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CREATION_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
CREATED_BY VARCHAR(50) NOT NULL,
LAST_UPDATE_IP VARCHAR(50) NOT NULL,
VERSION INT8 NOT NULL,
ATTRIBUTE_01 VARCHAR(255),
ATTRIBUTE_02 VARCHAR(255),
ATTRIBUTE_03 VARCHAR(255),
ATTRIBUTE_04 VARCHAR(255),
ATTRIBUTE_05 VARCHAR(255),
ATTRIBUTE_06 VARCHAR(255),
ATTRIBUTE_07 VARCHAR(255),
ATTRIBUTE_08 VARCHAR(255),
ATTRIBUTE_09 VARCHAR(255),
ATTRIBUTE_10 VARCHAR(255)
);

/***************************************************************************/
-- get_temptable_byrowid
--功能:将按照 CREATION_DATE 排序的表返回某段记录
--本例使用临时表
/*--------------------------------------------------------------------------*/
drop procedure if exists get_temptable_byrowid;
create procedure get_temptable_byrowid(v_start int,v_end int)
define v_skip int;
define v_first int;

/*
SET DEBUG FILE TO '/home/informix/info_debug.txt';
TRACE 'BEGIN TRACE' ;
trace on;
*/
let v_skip=v_start;
let v_first=v_end- v_start;

--1 创建临时表(存放ROWID)
drop table if exists t_tmp_rowid;

create temp table t_tmp_rowid(tmp_rowid int);

insert into t_tmp_rowid select skip v_skip first v_first rowid from demo_business_trip t1 order by t1.creation_date desc;

drop table if exists tmp_demo_business_trip;

--包含 tmp_rowid字段
/*
CREATE temp TABLE tmp_DEMO_BUSINESS_TRIP( tmp_rowid int, ID VARCHAR(50) NOT NULL, USER_ID VARCHAR(50) NOT NULL,
USER_NAME VARCHAR(50), ADDRESS VARCHAR(100), MATTER LVARCHAR(500), LENDMONEY INT8,
TRAFFIC VARCHAR(50), LEAVEDATE DATETIME YEAR TO FRACTION (5), BACKDATE DATETIME YEAR TO FRACTION (5),
SUMMARY LVARCHAR(4000), ALLOWANCE DECIMAL(16,2), LAST_UPDATE_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL, CREATION_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
CREATED_BY VARCHAR(50) NOT NULL, LAST_UPDATE_IP VARCHAR(50) NOT NULL, VERSION INT8 NOT NULL,
ATTRIBUTE_01 VARCHAR(255), ATTRIBUTE_02 VARCHAR(255), ATTRIBUTE_03 VARCHAR(255), ATTRIBUTE_04 VARCHAR(255),
ATTRIBUTE_05 VARCHAR(255), ATTRIBUTE_06 VARCHAR(255), ATTRIBUTE_07 VARCHAR(255), ATTRIBUTE_08 VARCHAR(255),
ATTRIBUTE_09 VARCHAR(255), ATTRIBUTE_10 VARCHAR(255));

insert into tmp_DEMO_BUSINESS_TRIP select t2.tmp_rowid,t1.* from demo_business_trip t1, t_tmp_rowid t2 where t1.rowid =t2.tmp_rowid ;
*/
--不包含 tmp_rowid字段

CREATE temp TABLE tmp_DEMO_BUSINESS_TRIP( ID VARCHAR(50) NOT NULL, USER_ID VARCHAR(50) NOT NULL,
USER_NAME VARCHAR(50), ADDRESS VARCHAR(100), MATTER LVARCHAR(500), LENDMONEY INT8,
TRAFFIC VARCHAR(50), LEAVEDATE DATETIME YEAR TO FRACTION (5), BACKDATE DATETIME YEAR TO FRACTION (5),
SUMMARY LVARCHAR(4000), ALLOWANCE DECIMAL(16,2), LAST_UPDATE_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL, CREATION_DATE DATETIME YEAR TO FRACTION (5) NOT NULL,
CREATED_BY VARCHAR(50) NOT NULL, LAST_UPDATE_IP VARCHAR(50) NOT NULL, VERSION INT8 NOT NULL,
ATTRIBUTE_01 VARCHAR(255), ATTRIBUTE_02 VARCHAR(255), ATTRIBUTE_03 VARCHAR(255), ATTRIBUTE_04 VARCHAR(255),
ATTRIBUTE_05 VARCHAR(255), ATTRIBUTE_06 VARCHAR(255), ATTRIBUTE_07 VARCHAR(255), ATTRIBUTE_08 VARCHAR(255),
ATTRIBUTE_09 VARCHAR(255), ATTRIBUTE_10 VARCHAR(255));

insert into tmp_DEMO_BUSINESS_TRIP select t1.* from demo_business_trip t1, t_tmp_rowid t2 where t1.rowid =t2.tmp_rowid ;

end procedure;

--测试
>

> execute procedure get_temptable_byrowid(0,15);
select id from tmp_DEMO_BUSINESS_TRIP;

Elapsed time: 0.011 sec

>

id

8a58a6c757d1497a0157d5a166e84547
8a58a6c757d1497a0157d5a1651b44ff
8a58a6c757d1497a0157d598347042c3
8a58a6c757d1497a0157d58356066a89
8a58a6c757d1497a0157d583557b6a79
8a58a6c757d1497a0157d57ce7976724
8a58a6c757d1497a0157d57ce4d566cd
8a58a6c757d1497a0157d57ce40f66b8
8a58a6c757d1497a0157d57ce3156696
8a58a6c857d149c20157d578498f5a9b
8a58a6c757d1497a0157d57814d36131
8a58a6c757d1497a0157d578153c6144
8a58a6c757d1497a0157d57817d8617a
8a58a6c857d149c20157d5691175507e
8a58a6c857d149c20157d5690be44ffd

15 row(s) retrieved.

>
execute procedure get_temptable_byrowid(6111115,6111130);
select id from tmp_DEMO_BUSINESS_TRIP;

Elapsed time: 1.669 sec

>

id

363E633D76743BD0E0534826D80A6073
363E633D76753BD0E0534826D80A6073
363E633D76763BD0E0534826D80A6073
363E633D76773BD0E0534826D80A6073
363E633D76783BD0E0534826D80A6073
363E633D76793BD0E0534826D80A6073
363E633D767A3BD0E0534826D80A6073
363E633D767B3BD0E0534826D80A6073
363E633D767C3BD0E0534826D80A6073
363E633D767D3BD0E0534826D80A6073
363E633D767E3BD0E0534826D80A6073
363E633D767F3BD0E0534826D80A6073
363E633D76803BD0E0534826D80A6073
363E633D76813BD0E0534826D80A6073
363E633D76823BD0E0534826D80A6073
15 row(s) retrieved.

Elapsed time: 0.001 sec

> execute procedure get_temptable_byrowid(5111115,5111130);
select id from tmp_DEMO_BUSINESS_TRIP;

Elapsed time: 1.405 sec

>

363E634CB8373BD0E0534826D80A6073
363E634CB8383BD0E0534826D80A6073
363E634CB8393BD0E0534826D80A6073
363E634CB83A3BD0E0534826D80A6073
363E634CB83B3BD0E0534826D80A6073
363E634CB83C3BD0E0534826D80A6073
363E634CB83D3BD0E0534826D80A6073
363E634CB83E3BD0E0534826D80A6073
363E634CB83F3BD0E0534826D80A6073
363E634CB8403BD0E0534826D80A6073
363E634CB8413BD0E0534826D80A6073
363E634CB8423BD0E0534826D80A6073
363E634CB8433BD0E0534826D80A6073
363E634CB8443BD0E0534826D80A6073
363E634CB8453BD0E0534826D80A6073
15 row(s) retrieved.

Elapsed time: 0.001 sec

>
execute procedure get_temptable_byrowid(50,65);
select id from tmp_DEMO_BUSINESS_TRIP;

Elapsed time: 0.011 sec

>

id