My database is oracle 11g, I use JBPM 5.4, There are 9654 records in TASK table, But getTasksAssignedAsPotentialOwner is very slow, every execute will cost more than 3 seconds.
Anybody has idea to optimize it? thanks.
Sql:
select task0_.id as col_0_0_, task0_.processInstanceId as col_1_0_,
names5_.text as col_2_0_, subjects3_.text as col_3_0_,
descriptio4_.text as col_4_0_, task0_.status as col_5_0_,
task0_.priority as col_6_0_, task0_.skipable as col_7_0_,
task0_.actualOwner_id as col_8_0_, task0_.createdBy_id as col_9_0_,
task0_.createdOn as col_10_0_, task0_.activationTime as col_11_0_,
task0_.expirationTime as col_12_0_, task0_.processId as col_13_0_,
task0_.processSessionId as col_14_0_ from Task task0_
left outer join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id
left outer join OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id
left outer join I18NText subjects3_ on task0_.id=subjects3_.Task_Subjects_Id
left outer join I18NText descriptio4_ on task0_.id=descriptio4_.Task_Descriptions_Id
left outer join I18NText names5_ on task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_
where task0_.archived=0 and (organizati6_.id='ff8080813962106e0139628fcd28000e' or organizati6_.id in ('chm.teamLeader', 'chm.approver', 'chm.operator')) and
(organizati6_.id in (select potentialo9_.entity_id from PeopleAssignments_PotOwners potentialo9_
where task0_.id=potentialo9_.task_id)) and (names5_.language='en-UK' or (select count(names10_.Task_Names_Id)
from I18NText names10_ where task0_.id=names10_.Task_Names_Id)=0) and (subjects3_.language='en-UK' or
(select count(subjects11_.Task_Subjects_Id) from I18NText subjects11_ where task0_.id=subjects11_.Task_Subjects_Id)=0)
and (descriptio4_.language='en-UK' or (select count(descriptio12_.Task_Descriptions_Id) from I18NText descriptio12_
where task0_.id=descriptio12_.Task_Descriptions_Id)=0) and (task0_.status in ('Created' , 'Ready' , 'Reserved' , 'InProgress' , 'Suspended'))
and (task0_.expirationTime is null) order by task0_.id desc;
query explain:
