今天调试一个sql语句,可以查询出来,且结果集是对的,但是开发组长让调优一下。
弄了很长时间,还是没有完成优化,
语句如下: select *
from tb_core_settlement ts
where ts.id in (select ins.jbpmobjectid
from processinstance ins
where ins.jbpmprocessid in
(select jti.procinst_
from jbpm_TaskInstance jti
where jti.token_ in
(select distinct taskinstan0_.TOKEN_
from JBPM_TASKINSTANCE taskinstan0_,
JBPM_POOLEDACTOR pooledacto1_
where (taskinstan0_.END_ is not null)
and (taskinstan0_.PROCINST_ in
(select taskinstan2_.PROCINST_
from JBPM_TASKINSTANCE taskinstan2_,
JBPM_PROCESSINSTANCE processins3_
where taskinstan2_.PROCINST_ =
processins3_.ID_
and (taskinstan2_.END_ is null)))
and pooledacto1_.ACTORID_ =
'8a9594e53227b459013227c1eff40065'
and pooledacto1_.ID_ = any
-- in
(select pooledacto4_.POOLEDACTOR_
from JBPM_TASKACTORPOOL pooledacto4_
where taskinstan0_.ID_ =
pooledacto4_.TASKINSTANCE_)
or taskinstan0_.ACTORID_ =
'8a9594e53227b459013227c1eff40065')
and jti.isSuspended_ != 1
and jti.isOpen_ = 1))语句很长且性能很低下
后来同事帮解决,语句如下,但是不清楚如何做的优化,而且看不明白如何从上面复杂的语句变为一段简洁sql语句,请sql高手帮分析,谢谢
select distinct ts.*
from tb_core_settlement ts, processinstance ins, jbpm_TaskInstance jti
where ts.id = ins.jbpmobjectid
and ins.jbpmprocessid = jti.procinst_
and jti.token_ in
(select distinct taskinstan0_.TOKEN_
from jbpm_TaskInstance taskinstan0_
where taskinstan0_.ACTORID_ = '8a9594e53218f1a60132197c22c2045f'
and jti.isSuspended_ != 1
and jti.isOpen_ = 1)
百分感谢帮分析!!!
弄了很长时间,还是没有完成优化,
语句如下: select *
from tb_core_settlement ts
where ts.id in (select ins.jbpmobjectid
from processinstance ins
where ins.jbpmprocessid in
(select jti.procinst_
from jbpm_TaskInstance jti
where jti.token_ in
(select distinct taskinstan0_.TOKEN_
from JBPM_TASKINSTANCE taskinstan0_,
JBPM_POOLEDACTOR pooledacto1_
where (taskinstan0_.END_ is not null)
and (taskinstan0_.PROCINST_ in
(select taskinstan2_.PROCINST_
from JBPM_TASKINSTANCE taskinstan2_,
JBPM_PROCESSINSTANCE processins3_
where taskinstan2_.PROCINST_ =
processins3_.ID_
and (taskinstan2_.END_ is null)))
and pooledacto1_.ACTORID_ =
'8a9594e53227b459013227c1eff40065'
and pooledacto1_.ID_ = any
-- in
(select pooledacto4_.POOLEDACTOR_
from JBPM_TASKACTORPOOL pooledacto4_
where taskinstan0_.ID_ =
pooledacto4_.TASKINSTANCE_)
or taskinstan0_.ACTORID_ =
'8a9594e53227b459013227c1eff40065')
and jti.isSuspended_ != 1
and jti.isOpen_ = 1))语句很长且性能很低下
后来同事帮解决,语句如下,但是不清楚如何做的优化,而且看不明白如何从上面复杂的语句变为一段简洁sql语句,请sql高手帮分析,谢谢
select distinct ts.*
from tb_core_settlement ts, processinstance ins, jbpm_TaskInstance jti
where ts.id = ins.jbpmobjectid
and ins.jbpmprocessid = jti.procinst_
and jti.token_ in
(select distinct taskinstan0_.TOKEN_
from jbpm_TaskInstance taskinstan0_
where taskinstan0_.ACTORID_ = '8a9594e53218f1a60132197c22c2045f'
and jti.isSuspended_ != 1
and jti.isOpen_ = 1)
百分感谢帮分析!!!
from tb_core_settlement ts
where ts.id in (select ins.jbpmobjectid
from processinstance ins
where ins.jbpmprocessid in
(select jti.procinst_
from jbpm_TaskInstance jti
where jti.token_ in
(select distinct taskinstan0_.TOKEN_
from jbpm_TaskInstance taskinstan0_
where taskinstan0_.ACTORID_ =
'8a9594e531d615480131d651ed1400a5')
and jti.isSuspended_ != 1
and jti.isOpen_ = 1))这段sql的in可以优化为关联查询么,为何可以那样改?
还有就是里面的语句,比如select * from a,b where a.id = b.aid and a.name = b.aname or a.age = b.aage。这条语句的话是不是可以解释为select * from a,b where (a.id = b.aid and a.name = b.aname )or a.age = b.aage