[jboss-jira] [JBoss JIRA] (HIBERNATE-164) Hibernate Criteria SQL select with order by causes in DB2 big performance impact
[
https://issues.jboss.org/browse/HIBERNATE-164?page=com.atlassian.jira.plu...
]
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)