Hi,
In our projects we use, in the past, hibernate 3.4 and we realize there´s a big problem with paging if the results were many, for example 10000 and the number of rows in the page was 10. If we click in the last page the SQL took forever...
The reason of the problem was because in the background the JPA make the TOP(10000) in the select and this is very time consuming in the SQL engine.
Because of this we evolve to the hibernate 4.1.4 and the paging was solved because of the dialect used Sql2005Dialect and resolve the paging problem making the query like this WITH query AS (... ROW_NUMBER() OVER... But doing further tests we realize that the sorting didn´t work when the select have inner selects like this :
Code:
WITH query AS (select viaalerthi0_.ALERT_HISTORY_ID as col_0_0_
,(select viamessage7_.ADDRESS
from ViaCTT.VIA_MESSAGE viamessage7_
where viamessage7_.MESSAGE_ID=viaalerthi0_.MESSAGE_ID
) as col_7_0_
, ROW_NUMBER()
OVER (order by viaalerthi0_.ALERT_HISTORY_ID desc) as __hibernate_row_nr__
from VIACTT.VIA_ALERT_HISTORY viaalerthi0_
cross join ViaCTT.VIA_USER_V viauserv1_
where viaalerthi0_.USER_ID=viauserv1_.USER_ID
and viauserv1_.POSTAL_BOX_ID=46477 )
SELECT col_0_0_, col_7_0_ FROM query WHERE __hibernate_row_nr__ >= 1 AND __hibernate_row_nr__ < 10
The sorting
order by viaalerthi0_.ALERT_HISTORY_ID desc dosen´t work.
After searching we realize there´s a new version of the hibernate 4.1.5 SP1 and we try it.
The results for sorting was resolved but the performance issues that we have in the hibernate 3.4 were back because of the TOP(?) making the paging a living hell when evolving to the latest pages.
Anyone have this problem? Or realized that there´s a problem in the hibernate paging, or i´m mistaken and i´m not seeing the best approach to this problem?
We use the SQL SERVER 2005 version.
Cheers,
Nuno Ferreira
Login
FAQ
Search
