[jboss-jira] [JBoss JIRA] (HIBERNATE-164) Hibernate Criteria SQL select with order by causes in DB2 big performance impact

[Petr Hariprasad Hajič (Jira)]

Petr Hariprasad Hajič created HIBERNATE-164:
-----------------------------------------------

             Summary: Hibernate Criteria SQL select with order by causes in DB2 big performance impact
                 Key: HIBERNATE-164
                 URL: https://issues.jboss.org/browse/HIBERNATE-164
             Project: Hibernate Integration
          Issue Type: Bug
         Environment: Oracle Java 7, Hibernate 3.6.10.Final, Tomcat 7.0, IBM DB2 9, 10 (not tested on the last version).
            Reporter: Petr Hariprasad Hajič
            Assignee: Steve Ebersole


Using detached criteria with ordering clause produces inefficient SQL (simplified).
??	DetachedCriteria crit = DetachedCriteria.forClass(Log.class);
...
	crit.addOrder(org.hibernate.criterion.Order.asc("dateTime");
??

Catched hibernate QUERY in console (simplified and replaced ? with values):

??select temp.* from ( select row_number() over(order by a.dateTime asc) as rownumber, a.dateTime, a.*
from T_LOG a order by a.dateTime asc) as temp where rownumber <= 75;??

Cost of this SQL is about 2*10^6 - very pure (index was used).

Slightly improved SQL gives good performance:

??select temp.* from ( select row_number() over(order by a.dateTime asc) as
 rownumber, a.dateTime, a.* from T_LOG a) as temp where rownumber <= 75;??

Cost is about 75 (i.e. nearly milion times better), of course index was used also! 

But I don´t know how to modify criteria to omit 
But I dont know how to modify Criteria to exclude second wrong order by.

Is there solution or workaround?







--
This message was sent by Atlassian Jira
(v7.12.1#712002)