Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Problem paging and sorting with hibernate 4 (JPA 2)
PostPosted: Mon Aug 06, 2012 1:30 pm 
Newbie

Joined: Fri Oct 06, 2006 10:10 am
Posts: 7
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


Top
 Profile  
 
 Post subject: Re: Problem paging and sorting with hibernate 4 (JPA 2)
PostPosted: Thu Aug 09, 2012 4:58 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 988
What it the exact query produced by version 4.1.5 SP1 ?
Can you also post the code where you define the query (pagination calls included)?


Top
 Profile  
 
 Post subject: Re: Problem paging and sorting with hibernate 4 (JPA 2)
PostPosted: Thu Aug 09, 2012 7:40 am 
Newbie

Joined: Fri Oct 06, 2006 10:10 am
Posts: 7
Hi,

Here is the query generated by the version 4.1.5 SP1.
Code:
WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( select TOP(80) viaentity6_.POSTAL_BOX_ID as col_0_0_, viapostalb8_.CODE as col_1_0_, viaaccount1_.ACCOUNT_ID as col_2_0_, viaaccount1_.ACCOUNT_NAME as col_3_0_, viadocumen0_.DOCUMENT_ID as col_4_0_ (,...)
FROM VIA_DOCUMENT viadocumen0_ cross join VIA_ACCOUNT viaaccount1_ inner join VIA_RESOURCE viaaccount1_1_ on viaaccount1_.ACCOUNT_ID=viaaccount1_1_.RESOURCE_ID, VIA_ENTITY viaentity6_ inner join VIA_RESOURCE viaentity6_1_ on viaentity6_.ENTITY_ID=viaentity6_1_.RESOURCE_ID, VIA_POSTAL_BOX viapostalb8_ inner join VIA_RESOURCE viapostalb8_1_ on viapostalb8_.POSTAL_BOX_ID=viapostalb8_1_.RESOURCE_ID cross join VIA_BILLER viabiller2_ cross join VIA_DOCUMENT_TYPE viadocumen3_ cross join VIA_DOCUMENT_DELIVERY_TYPE viadocumen4_ cross join VIA_LOT vialot5_
WHERE viaaccount1_.ENTITY_ID=viaentity6_.ENTITY_ID
(and ...) order by viadocumen0_.DOCUMENT_ID DESC ) inner_query ) SELECT col_0_0_, col_1_0_, col_2_0_, col_3_0_, col_4_0_, (...) FROM query WHERE __hibernate_row_nr__ >=  71  AND __hibernate_row_nr__ <  81

The problem is that the inner query obtain all the results and then is filtered by the outer query. When we get a large page, say 1000 page, the query stalls... Because the top gets all the results... this have a performance issue.

If you are asking if we use the setMaxResults() method we are using.
Code:
...
query.setFirstResult(filter.getFirstResult());
query.setMaxResults(filter.getMaxResults());

return query.getResultList();


Cheers,

Nuno


Top
 Profile  
 
 Post subject: Re: Problem paging and sorting with hibernate 4 (JPA 2)
PostPosted: Fri Aug 17, 2012 8:19 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 988
The inner query does'nt get all the results, it get's the first 80 results (select TOP(80)), not ?


Top
 Profile  
 
 Post subject: Re: Problem paging and sorting with hibernate 4 (JPA 2)
PostPosted: Fri Aug 17, 2012 9:10 am 
Newbie

Joined: Fri Oct 06, 2006 10:10 am
Posts: 7
Offcourse it does... This is an example...

The question (performance issue) is when you have 150000 pages and you click on the last one you get TOP (150000) and this is the problem, it gets all the results... (like i said in the post).


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.