前些天在看javaeye的时候，看到一篇说hibernate如果使用sql server数据源的话，分页会有效率问题。
当是看过就算了，今天突然想起来，我们oecp社区的持久层数据源也是sql server，于是尽早来了就做了下测试。
结果如下：第一页：start: 0 limit :10
select
top 10 activityph0_.pk as pk3_,
activityph0_.comment as comment3_,
activityph0_.replyTime as replyTime3_,
activityph0_.replyTo_pk as replyTo6_3_,
activityph0_.replyer_pk as replyer7_3_,
activityph0_.replyerName as replyerN4_3_,
activityph0_.summary as summary3_,
activityph0_.activityPhoto_pk as activity8_3_
from
t_activity_photo_comment activityph0_
where
activityph0_.activityPhoto_pk=?
order by
activityph0_.replyTime desc
第二页 ： start :10 limit :10
select
top 20 activityph0_.pk as pk3_,
activityph0_.comment as comment3_,
activityph0_.replyTime as replyTime3_,
activityph0_.replyTo_pk as replyTo6_3_,
activityph0_.replyer_pk as replyer7_3_,
activityph0_.replyerName as replyerN4_3_,
activityph0_.summary as summary3_,
activityph0_.activityPhoto_pk as activity8_3_
from
t_activity_photo_comment activityph0_
where
activityph0_.activityPhoto_pk=?
order by
activityph0_.replyTime desc
果然和原文说的一致，hibernate下的Sql server分页是伪分页，一次top 20条记录，我们可以试想下，如果当论坛的访问量上去了，一次top数千数万条记录，同时还有并发，将会是严重的性能问题。
Hibernate的好处之一就是分装了不同数据库的方言，orm的同时，操作具有数据库无关性。
在家里试过了oracle的分页sql，果然是性能较好的三层select rownum操作的经典算法，性能相对较高。
看来确实是Hibernate对于sql server的方言封装出了问题（难道是开源框架对Sql server表现出的不满么，恶意猜想中）。
原文中给出了sql server的优化方式，我仔细看了下代码，确实能有效地对于Sql server的分页操作提供了有效地优化方式。
http://www.javaeye.com/topic/721903
