SQL Query performance is slow when joining varaible table

[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.

[jbarrez]

The first thing to investigate is what an additional index would do.
Activiti has some out of the box indices, but the idea is generally that you tweak the DB schema to your need when it comes to indices.

[ganeshr]

Thank you.