后果:
查询时,10000条记录要7分钟!
关联关系:
[code] public class TrailRecord {
Long id,
Knowledge knowledge,
Date borwseTime
}
public class TrailSession {
Long id,
User user,
}
[/code]
查询HQL代码:
[code] getHibernateTemplate().setMaxResults(amount);
String hql = "select r.knowledge from TrailRecord r where r.browseTime in (select max(t.browseTime) as btime from TrailRecord t where t.trailSession.user=? and t.knowledge.deleted=false group by t.knowledge) order by r.browseTime desc";
return getHibernateTemplate().find(hql, new Object[]{user});[/code]
生成的SQL:
[code]select knowledge1_.N_BH as N1_0_,
knowledge1_.B_Deleted as B2_0_,
knowledge1_.C_Content as C3_0_,
knowledge1_.D_CreateDate as D4_0_,
knowledge1_.N_Type as N15_0_
from T_TrailRecord trailrecor0_
inner join T_Knowledge knowledge1_ on trailrecor0_.N_Knowledge =
knowledge1_.N_BH
where trailrecor0_.D_BrowseTime in
(select max(trailrecor2_.D_BrowseTime)
from T_TrailRecord trailrecor2_,
T_TrailSession trailsessi3_,
T_Knowledge knowledge4_
where trailrecor2_.N_TrailSession = trailsessi3_.N_BH
and trailrecor2_.N_Knowledge = knowledge4_.N_BH
and trailsessi3_.N_User = 1
and knowledge4_.B_Deleted = 0
group by trailrecor2_.N_Knowledge)
order by trailrecor0_.D_BrowseTime desc limit 11[/code]
问题已经解决了,但是还是没有完全弄明白生成的SQL为什么这么慢。请帮忙分析下。
查询时,10000条记录要7分钟!
关联关系:
[code] public class TrailRecord {
Long id,
Knowledge knowledge,
Date borwseTime
}
public class TrailSession {
Long id,
User user,
}
[/code]
查询HQL代码:
[code] getHibernateTemplate().setMaxResults(amount);
String hql = "select r.knowledge from TrailRecord r where r.browseTime in (select max(t.browseTime) as btime from TrailRecord t where t.trailSession.user=? and t.knowledge.deleted=false group by t.knowledge) order by r.browseTime desc";
return getHibernateTemplate().find(hql, new Object[]{user});[/code]
生成的SQL:
[code]select knowledge1_.N_BH as N1_0_,
knowledge1_.B_Deleted as B2_0_,
knowledge1_.C_Content as C3_0_,
knowledge1_.D_CreateDate as D4_0_,
knowledge1_.N_Type as N15_0_
from T_TrailRecord trailrecor0_
inner join T_Knowledge knowledge1_ on trailrecor0_.N_Knowledge =
knowledge1_.N_BH
where trailrecor0_.D_BrowseTime in
(select max(trailrecor2_.D_BrowseTime)
from T_TrailRecord trailrecor2_,
T_TrailSession trailsessi3_,
T_Knowledge knowledge4_
where trailrecor2_.N_TrailSession = trailsessi3_.N_BH
and trailrecor2_.N_Knowledge = knowledge4_.N_BH
and trailsessi3_.N_User = 1
and knowledge4_.B_Deleted = 0
group by trailrecor2_.N_Knowledge)
order by trailrecor0_.D_BrowseTime desc limit 11[/code]
问题已经解决了,但是还是没有完全弄明白生成的SQL为什么这么慢。请帮忙分析下。