Hibernate下DB2分页算法分析

2016-06-18 16:55:02

DB2分页算法分析

当数据量在百万级以上：
1、查看前几页数据时，hibernate 4.3.11 的查询性能高于 hibernate 3.6.10；
2、随着页数增加，hibernate 4.3.11 的查询性能逐渐降低；

性能对比结果：

Hibernate3性能差异：

Hibernate4性能差异：

分析过程

-- hibernate 3.6.10
-- DB2Dialect 分页算法
-- 1、首页
select * from ( select rownumber() over(ORDER BY a.sort_keys DESC ) AS rownumber_, 
a.* FROM user_table_name a ORDER BY a.sort_keys DESC 
 ) as temp_ where rownumber_ < 10
; 
-- 2、分页
select * from ( select rownumber() over(ORDER BY a.sort_keys DESC ) AS rownumber_, 
a.* FROM user_table_name a ORDER BY a.sort_keys DESC 
 ) as temp_ where rownumber_ between 11 and 20
;
-------------------------------------------------------------
-- hibernate 4.3.11 
-- DB2Dialect 分页算法
-- 1、首页
SELECT * FROM user_table_name ORDER BY sort_keys DESC 
fetch first 10 rows ONLY
;
-- 2、分页
select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from (
SELECT * FROM user_table_name ORDER BY sort_keys DESC 
fetch first 100  rows only ) as inner2_ ) as inner1_ where rownumber_ > 90
order by rownumber_
;