AnsweredAssumed Answered

SQL Query performance is slow when joining varaible table

Question asked by ganeshr on Dec 23, 2013
Latest reply on Dec 26, 2013 by ganeshr
Hi,

   Here is the query I'm using and when executing it is almost taking 4-5 seconds. but when I remove joining variable table, then query is returning in milliseconds

select count(distinct task.ID_)
  from ACT_RU_TASK task
   inner join ACT_RU_IDENTITYLINK identity
    on identity.TASK_ID_ = task.ID_ 
   inner join ACT_RU_VARIABLE var
    on var.TASK_ID_ = task.ID_
  where task.NAME_ = 'ActHumanTask'
     and ( (task.ASSIGNEE_ is NULL and exists
       (select identity1.group_id_
          from ACT_RU_IDENTITYLINK identity1
          where identity1.group_id_ = ('BPM-User')
          and identity.task_id_ = identity1.task_id_ ) ) or  task.ASSIGNEE_ = 'ganeshr')
     and ( var.NAME_ = 'TaskCurrentState' and var.TEXT_ = 'ACTIVE' )

  Can any one suggest for performance improvement.

thanks.

Outcomes