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

[Petr Hariprasad Hajič (Jira)]

     [ https://issues.jboss.org/browse/HIBERNATE-164?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Petr Hariprasad Hajič updated HIBERNATE-164:
--------------------------------------------
    Description: 
Using detached criteria with ordering clause produces inefficient SQL (simplified).


{code:java}
DetachedCriteria crit = DetachedCriteria.forClass(Log.class);
...
crit.addOrder(org.hibernate.criterion.Order.asc("dateTime");

{code}

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


{noformat}
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;

{noformat}


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

Slightly improved SQL gives good performance:


{noformat}
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;

{noformat}

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

But I dont know how to modify Criteria to exclude second wrong order by.

Is there solution or workaround?





  was:
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 dont know how to modify Criteria to exclude second wrong order by.

Is there solution or workaround?







> 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
>            Priority: Major
>              Labels: support
>
> Using detached criteria with ordering clause produces inefficient SQL (simplified).
> {code:java}
> DetachedCriteria crit = DetachedCriteria.forClass(Log.class);
> ...
> crit.addOrder(org.hibernate.criterion.Order.asc("dateTime");
> {code}
> Catched hibernate QUERY in console (simplified and replaced ? with values):
> {noformat}
> 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;
> {noformat}
> Cost of this SQL is about 2*10^6 - very pure (index was used).
> Slightly improved SQL gives good performance:
> {noformat}
> 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;
> {noformat}
> Cost is about 75 (i.e. nearly milion times better), of course index was used also! 
> 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)